Right to left search

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)

Hi Martin,

Can you give an example of what you're trying to do?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Martin" <Martin@discussions.microsoft.com> wrote in message news:D67290FC-3D9C-4458-80B6-9AD691955440@microsoft.com...
| 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)
|

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
>
> "Martin" <Martin@discussions.microsoft.com> wrote in message news:D67290FC-3D9C-4458-80B6-9AD691955440@microsoft.com...
> | 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)
> |
>
>
>

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


"Martin" <Martin@discussions.microsoft.com> wrote in message news:122641E2-287D-4CF6-9C93-8F4F5EF4C5BC@microsoft.com...
| 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
| >
| > "Martin" <Martin@discussions.microsoft.com> wrote in message news:D67290FC-3D9C-4458-80B6-9AD691955440@microsoft.com...
| > | 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)
| > |
| >
| >
| >

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

Martin wrote:
>
> 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
> >
> > "Martin" <Martin@discussions.microsoft.com> wrote in message news:D67290FC-3D9C-4458-80B6-9AD691955440@microsoft.com...
> > | 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)
> > |
> >
> >
> >

--

Dave Peterson