Question for Bob Phillips re Splitting Names from Cells

[Paul Sheppard]

Bob

You gave the answers below for splitting names from cells:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,
",""))))-1)

and

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

Using these formulas on this example John A Doe results in John A an
Doe, is it possible to split it to show John / A / Doe in 3 separat
cells, I know I could use the formulas again on the John A result t
split them but I'd like to do it in 1 go

If possible could you explain what the formula is doing, I understan
LEFT and LEN etc but don't understand the use of ^^

Thanks

Pau

 

[Bob Phillips]

Hi Paul,

It goes through a number of steps

- find the number of spaces in the text - LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
- replace the final space with ^^ (to uniquify it) - SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
- then find the position of that unique string - FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
- which we can then use to extract the text before or after the final space.

So in our example, we convert John A,. Doe to John A.^^Doe, which gives us a
simple way to extract John A. and Doe.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Sheppard" <[email protected]>
wrote in message
news[email protected]...

 

[Paul Sheppard]

Bob

Thanks for the response, I was able to use the formulae to extrac
names, but didn't really understand how it was doing it, now I do

Always good to understand the answer as opposed to just blindly usin
i

 

[Ron de Bruin]

Hi Paul

See also DataRefiner for a few text formulas
http://www.rondebruin.nl/datarefiner.htm

 

[Guest]

Uniquify? Is that a technical term? <g>

Hi Paul,

It goes through a number of steps

- find the number of spaces in the text - LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
- replace the final space with ^^ (to uniquify it) - SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
- then find the position of that unique string - FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
- which we can then use to extract the text before or after the final space.

So in our example, we convert John A,. Doe to John A.^^Doe, which gives us a
simple way to extract John A. and Doe.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Sheppard" <[email protected]>
wrote in message

 

[Bob Phillips]

I know it is horrible, but it was one made up word, or many real ones. I
apologise unreservedly <bg>

Bob

 

[Guest]

With regards to your question to split First Name / Middle / Last all in one
go - is all of your data either First Name / Middle Initial / Last Name or
First Name / Last Name?

If so, you could change the formula to find the first name to

=LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)

Find the middle name or initial (shamelessly stealing from some of Bob's
posts):
=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))>1,MID(TRIM(A1),FIND("
",TRIM(A1))+1,FIND(" ",SUBSTITUTE(TRIM(A1)," ","^^",1))-FIND("
",TRIM(A1))-2),"")

The challenge I see is if some of your names have multiple words comprising
either the first name or last name or are Juniors/Seniors

John Q Doe Jr.
Mary Anne Mae Smith

 

[Guest]

I don't have an unabridged dictionary - you could have bluffed <g>

 

[Bob Phillips]

I have a RegExp solution for any combination. It gets worse, because you can
have name like William A. Carson Jr., or Ian St. John, or even Marquis de
Sade.