Help me!

[Razzak]

Please help me. I would like to separate one word from a cell.

Example : Cell A2 = The Quick Brown Fox

Question-1: How to separate "Fox" from Cell A2?
Question-2: How to separate "Brown" from Cell A2?

 

[fake_be]

Hi Razzak,

To search for the last word you can use the formula below.
=RIGHT(A2;LEN(A2)-FIND(" ";A2))

If you have 3 words then you can use this formula.
=LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-
FIND(" ",C45,1)),1))
(replace C45 with the cellreference you are using.

Haven't got the formula for the 3 word (or prior to the last word).

Good luck,
Tom

 

[Mike H]

Hi,

to get the last word use

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))


and to get the second to last no matter how many words there are use

=MID(MID(MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1),1,256),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),1,256),FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),256))-2)

Mike

 

[Jacob Skaria]

Use SUBSTITUTE function; (case sensitve)

For replacing string use
=TRIM(SUBSTITUTE(A2,"Fox",)

For replacing the word "Fox".
=TRIM(SUBSTITUTE(" " & A2 & " ", " " & "Fox" & " "," "))
'The Quick Brown Fox Foxer' will be converted to 'The Quick Brown Foxer'

If this post helps click Yes