How to Substract a word from right side of the column using Right(

[JAF-In]

Hi
How can i remove a Right side word from a set of words in one column
i need to substract the last names in to another column
eg: James William Watson... In this case i need to to take" Watson to the
next column

Regards

 

[Jacob Skaria]

With the full name in cell A1; try the below formula in B1 and copy down as
required

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

If this post helps click Yes

 

[Mike H]

Try this

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Mike

 

[JAF-In]

hi
if i want to retrieve more than one words
Eg: All names except last name?

Regards

 

[Mike H]

Hi,

Try this with your text string in a1 and the number of the word to extract
in b1

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

Mike

 

[Shane Devenshire]

Here is another approach:

1. Suppose the names are in A1:A100, select them and choose Data, Text to
Columns, Delimited, Next, check Space, Next, pick a destination cell, for
this example, C1, Finish.
2. Enter the following formula in B1 and fill it down
=SUBSTITUTE(A1,LOOKUP("ZZ",1:1),"")

convert column B to values and delete the results of step 1.