Easiest to do in several steps, once you're satisfied you can combine them in one formula.
First: how many spaces are there?
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Now substitute the last one by a special character, like #, also with a SUBSTITUTE formula
Now find the position of that character with the FIND function
Take the part to the right of this character with RIGHT(A1,LEN(A1)-the position you just found)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Sorry, should have done this earlier.
| I have a list of names of the type name1spacename2space....spacesurname and
| I am trying to quickly separate the surname from the rest. For example, three
| such names could be:
|
| John James
|
| John Jack James
|
| John Jack Jeremy James
|
| with the surname being James in all three cases.
|
| "Niek Otten" wrote:
|
| > Hi Martin,
| >
| > Can you give an example of what you're trying to do?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Does anyone know of a simple way to search text strings from right to left
| > | instead of left to right? I can buy software for this but thought there
| > | should be a non-cost option somewhere out there.
| > | I am using Microsoft Office Excel 2003 (2)
| > |
| >
| >
| >
