software

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 quot;Text to columnsquot;,
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.


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(quot; quot;,A1)-1)

In Cell C1 enter =TRIM(MID(A1,FIND(quot; quot;,A1) 1,50))--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: www.excelforum.com/member.php...oamp;userid=32763
View this thread: www.excelforum.com/showthread...hreadid=539165Thanks - I don't understand how the forulae work, but they do work (obviously
the quot;50quot; in the end of the second one limits the length of text to 50
characters...

Cheers
Chris

quot;CaptainQuattroquot; wrote:

gt;
gt; The following will work as long as don't have any middle names in your
gt; mix:
gt;
gt;
gt; In Cell A1 put the first and last name e.g.
gt;
gt; Adam Crabapple
gt;
gt; In Cell B1 enter formula =LEFT(A1,FIND(quot; quot;,A1)-1)
gt;
gt; In Cell C1 enter =TRIM(MID(A1,FIND(quot; quot;,A1) 1,50))
gt;
gt;
gt; --
gt; CaptainQuattro
gt; ------------------------------------------------------------------------
gt; CaptainQuattro's Profile: www.excelforum.com/member.php...oamp;userid=32763
gt; View this thread: www.excelforum.com/showthread...hreadid=539165
gt;
gt;


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 quot;=TRIMquot;
function eliminates all the blank spaces beyond the end of the name.--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: www.excelforum.com/member.php...oamp;userid=32763
View this thread: www.excelforum.com/showthread...hreadid=539165