Last Word in a Cell

[Shymanas]

Hi,

I have following words in a Cell:

John Peter Smith
Alex J Peter Albert

I want to move the last word only into a new cell.

So in the first example, Smith would go into the next cell and for the second example Albert would go into the next cell.

Any idea how to do this?

Thanks
Shai

 

[barry houdini]

With data in A2 try this formula in B2

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

 

[Shymanas]

Hi,

Thanks for this.

I also have a scenario where the name is:

Mr P. T. Smith

Is there is a full stop before the last word then it doesn't seem to work.

Any ideas?

Thanks
Shai

 

[Shymanas]

Actually for some reason the formula given is inconsistent.

It doesnt work for some fields but works for others and the fields are c=just different in content.

Thanks Shai

 

[Silvrhelm]

Hi I think the error in this formula comes when there is just a full stop and no space between the last part and the previous ie

Mr John P.T.Smith would cause an error but
Mr John P. T. Smith would not,

This can be solved by first running this formula to replace any full stops with a space, If data starts in A2...

=substitute(A2,"."," ")

Once you have ran this formula through all the entries, copy and paste special values only back into column A and re run the formula given above.

It should then work.

 

[barry houdini]

That's right - if the data is inconsistent you might need some more in the formula - this version would treat a . like a space and take everything after the last one of either of those - also modified to cope with spaces at the end of the data

=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"."," "))," ",REPT(" ",99)),99))

 

[Shymanas]

Thanks that worked a treat!

Shai