searching for a space " "

[wahamler]

I have a colum of data with names both first name and last. is there a
formula to pull out the first name and one to pull out the last name? a cell
might current read "John Smith" i want to separate the name so i have one
field reading "John" and the next field reading "Smith". The names will vary
in character length so i cannot use a simple left() command.

[Ron de Bruin]

Try this with the name in B5

First word
=IF(ISERR(FIND(" ",B5)),B5,IF(RIGHT(LEFT(B5,FIND(" ",B5)-1))=",",LEFT(B5,FIND(" ",B5)-2),LEFT(B5,FIND(" ",B5)-1)))

All except first word
=IF(ISERR(FIND(" ",B5)),"",MID(B5,FIND(" ",B5)+1,1024))

See also DateRefiner
http://www.rondebruin.nl/datarefiner.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"wahamler" <wahamler@discussions.microsoft.com> wrote in message news:3EB4B057-84FC-4244-93AF-73A6E6828D88@microsoft.com...
>I have a colum of data with names both first name and last. is there a
> formula to pull out the first name and one to pull out the last name? a cell
> might current read "John Smith" i want to separate the name so i have one
> field reading "John" and the next field reading "Smith". The names will vary
> in character length so i cannot use a simple left() command.

[RagDyer]

What is the make-up of the majority of your data?

If you really have just 2 names, you can use TTC (Text To Columns), where an
"odd-ball" (von Dunst ) could be handled manually.

Check out the Help files for this function, which yields (returns) actual
data within the cells, and not formulas.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"wahamler" <wahamler@discussions.microsoft.com> wrote in message
news:3EB4B057-84FC-4244-93AF-73A6E6828D88@microsoft.com...
> I have a colum of data with names both first name and last. is there a
> formula to pull out the first name and one to pull out the last name? a
cell
> might current read "John Smith" i want to separate the name so i have one
> field reading "John" and the next field reading "Smith". The names will
vary
> in character length so i cannot use a simple left() command.