Splitting text, like "Text to columns", but as a formula
 

I have first names and last names in one column that I want to split into two
columns. A formula woul be more practical than using the "Text to columns",
as I'm constantly adding to the spreadsheet, and need to rapidly get the one
column and two column versions out of it. I know LEFT and RIGHT work well for
fixed length situations, so I'm hoping there's similar comand that uses a
delimiter.

Splitting text, like "Text to columns", but as a formula
 

The following will work as long as don't have any middle names in your
mix:


In Cell A1 put the first and last name e.g.

Adam Crabapple

In Cell B1 enter formula =LEFT(A1,FIND(" ",A1)-1)

In Cell C1 enter =TRIM(MID(A1,FIND(" ",A1)+1,50))


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=539165

Splitting text, like "Text to columns", but as a formula
 

Thanks - I don't understand how the forulae work, but they do work (obviously
the "50" in the end of the second one limits the length of text to 50
characters...

Cheers
Chris

"CaptainQuattro" wrote:


The following will work as long as don't have any middle names in your
mix:


In Cell A1 put the first and last name e.g.

Adam Crabapple

In Cell B1 enter formula =LEFT(A1,FIND(" ",A1)-1)

In Cell C1 enter =TRIM(MID(A1,FIND(" ",A1)+1,50))


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=539165

Splitting text, like "Text to columns", but as a formula
 

You are right. 50 is an arbitrary number of characters, but the number
has to be big enough to accommodate the longest names that you might
have to parse.

It doesn't really matter how big the number is because the "=TRIM"
function eliminates all the blank spaces beyond the end of the name.


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=539165