Siple but what was the formula for splitting names?
 

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

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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Martyn" wrote in message
...
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

Siple but what was the formula for splitting names?
 

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

--
Message posted from http://www.ExcelForum.com

Siple but what was the formula for splitting names?
 

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

--
Message posted from http://www.ExcelForum.com

Siple but what was the formula for splitting names?
 

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 ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Siple but what was the formula for splitting names?
 

Pleasure` Martyn
Thanks for the feedback
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Martyn" wrote in message
...
Thanks a lot Max

Siple but what was the formula for splitting names?
 

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
_ _
^¢¯^
--

Siple but what was the formula for splitting names?
 

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" wrote in message
...
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

Siple but what was the formula for splitting names?
 

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

Siple but what was the formula for splitting names?
 

Thanks a lot Max

"Max" wrote in message
...
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 ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Siple but what was the formula for splitting names?
 

Thank you very much UKMatt

"UKMatt " wrote in message
...
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 lose
the W.

HTH
UKMatt


---
Message posted from http://www.ExcelForum.com/

Siple but what was the formula for splitting names?
 

And thank to you sir...:)

"mangesh_yadav " wrote in
message ...
Well, I may not have a shortcut, but depending on what you need, you
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.

-mangesh


---
Message posted from http://www.ExcelForum.com/

Siple but what was the formula for splitting names?
 

Thanks Soo...

"JulieD" wrote in message
...
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" wrote in message
...
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

Siple but what was the formula for splitting names?
 

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

"JulieD" wrote in message
...
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" wrote in message
...
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

Siple but what was the formula for splitting names?
 

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.

--
--
Fabian
Visit my website often and for long periods!
http://www.lajzar.co.uk