Siple but what was the formula for splitting names?

  • Thread starter Thread starter Max
  • Start date Start date
M

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

JulieD said:
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 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top