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

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
---------------
Jacob Skaria


"JAF-In" wrote:

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

Try this

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

Mike

"JAF-In" wrote:

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

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

Regards

"Mike H" wrote:

Try this

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

Mike

"JAF-In" wrote:

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

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

"JAF-In" wrote:

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

Regards

"Mike H" wrote:

Try this

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

Mike

"JAF-In" wrote:

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

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JAF-In" wrote:

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