Trouble extracting partial text from cell contents

Hi all,

I've been battling with this on and off for a while, but its time to wave the white flag and ask for help....

I have a cell containing 4 lines of text (well 7 when you include spacer lines), i am trying to get the last line into a cell by itself but keep hitting issues that i cant quite identify.

I have included an image of the formula i have so far in operation, and a workbook with it in use.

example1.jpg

i can see it is almost working but i have no idea why it is not starting at the Char(10) that its looking for? but rather sometimes a few characters before and sometimes after. i believe it is doing what i tell it to, just clearly i cant tell it to do the right thing!

Also, this is the potentially deadended rout i have gone down, if i have missed a more obvious way to do things i am more than happy to go with something that will work!

Please help

Dean

Try

=TRIM(RIGHT(SUBSTITUTE(B2,CHAR(10),REPT(" ",255)),255))

Jonmo1 - It is my understanding that "255" represents character spaces from the right. I tried this on a project that I am working on...works great!!

My question, what if my character spaces vary. Meaning I need to pull/return information that is different and their could be spaces within whats needed, example; West Virginia or Virginia.
My data does have a constant, it is the > symbol. Is there a way in the formula to return only the data to the right of that symbol?
Example: Cable Assemblies > Copper Cable Assemblies > Lighting Cable Assemblies
My return needs to be "Lighting Cable Assemblies"