Siple but what was the formula for splitting names?

[Max]

One way, assuming the names are all in a "2 word" structure:

In B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1)

In C1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99)
("99" is arbitrary, just choose a number high enough
to extract the max likely # of characters in the 2nd word)

Another easier? way to try is to use: Data > Text to columns

Select A1
Click Data > Text to columns > Next
In step2 of the wizard, check the box for "Space"
Click Finish

 

[mangesh_yadav]

Well, I may not have a shortcut, but depending on what you need, yo
could use the feature in the menu. Do the following:
Select the column in which you have the data to be split. Click Data
Text to columns. Select delimited and then space and ok.

-manges

 

[UKMatt]

Not pretty but you can use the following formulae:

B1=LEFT(A1,SEARCH(" ",A1,1)-1)

C1=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))

Of course if you have a name like "George W. Bush" in A1 you will los
the W.

HTH
UKMat

 

[Max]

Your PC clock/settings seem to be galloping ahead by ~14 hours
Perhaps time to check and correct your "Date and Time" settings
in Control Panel ..

 

[Max]

Pleasure` Martyn
Thanks for the feedback

 

[Soo Cheon Jheong]

Martyn,

- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
B1: =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)
C1: =RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1)))

or

B1: =REPLACE(TRIM(A1),FIND(" ",TRIM(A1)),LEN(A1),)
C1: =REPLACE(TRIM(A1),1,FIND(" ",TRIM(A1)),)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[JulieD]

Hi Martyn

alternatively you could use the data / text to columns feature - just ensure
you have a blank column to the right of your existing data for the names to
be split into.

Cheers
JulieD

 

[Martyn]

Hi,
I need the formula for splitting names...
i.e need "Martyn Williams" in A1 to be splitted to "Martyn" and "Williams"
in B1 and C1
TIA

 

[Martyn]

Thanks a lot Max

 

[Martyn]

Thank you very much UKMatt

 

[Martyn]

And thank to you sir...

 

[Martyn]

Thanks Soo...

Hi Martyn

alternatively you could use the data / text to columns feature - just ensure
you have a blank column to the right of your existing data for the names to
be split into.

Cheers
JulieD

 

[Martyn]

Thanks JulieD...and Soo
Now I have more then one alternatives

 

[Fabian]

Martyn hu kiteb:

Hi,
I need the formula for splitting names...
i.e need "Martyn Williams" in A1 to be splitted to "Martyn" and
"Williams" in B1 and C1

Others have proposed solutions. Just be aware that not all names will
fit this pattern. Consider Soo Cheon Jheong as an example. Peter van
helsing is another example that breaks the pattern.

Computer programmers that think they know how my name should be spelt
and capitalised is a pet peeve of mine. Companies have lost my account
over it before, including one that tried to argue with me face to face
over the spelling. The software had truncated my surname at the space.