Text Splitting easier? (Tokeniser?)

[Lee Harris]

I've a column of text, generally in the form

Surname, Forename


I want to split into two columns automatically with Forename and Surname
separately. I've been able to do it with MID and FIND, but it's quite clunky
and wondered if there was a quicker, easier or more elegant way just to
tokenise a string based on a defined separator (in this case the character ,
followed by a space)

tks in advance

 

[Biff]

Hi!

Don't know what your formulas look like but these will do the job:

Forename:

=MID(A1,FIND(", ",A1)+2,255)

Surname:

=LEFT(A1,FIND(", ",A1)-1)

Both, far from clunky!

You could use Data>Text to Columns but it's not automatic.

Biff

 

[Anne Troy]

Try selecting your column, and use Data-->Text to columns.
http://www.officearticles.com/excel/best_tips_for_using_microsoft_excel.htm
(About halfway down the page)
************
Anne Troy
www.OfficeArticles.com

 

[Guest]

I've a column of text, generally in the form

Surname, Forename


I want to split into two columns automatically with Forename and Surname
separately. I've been able to do it with MID and FIND, but it's quite clunky
and wondered if there was a quicker, easier or more elegant way just to
tokenise a string based on a defined separator (in this case the character ,
followed by a space)

tks in advance

 

[Ron Rosenfeld]

I've a column of text, generally in the form

Surname, Forename


I want to split into two columns automatically with Forename and Surname
separately. I've been able to do it with MID and FIND, but it's quite clunky
and wondered if there was a quicker, easier or more elegant way just to
tokenise a string based on a defined separator (in this case the character ,
followed by a space)

tks in advance

I don't know if it's more elegant to use regular expressions.

Laurent Longre has written a free add-in which allows the use of regular
expressions. You can download morefunc.xll from http://xcell05.free.fr/

The formulas to extract the 1st and 2nd word would then be, simply:

=REGEX.MID($A1,"\w*",1)
=REGEX.MID($A1,"\w*",2)


--ron

 

[Lee Harris]

Hi!

Don't know what your formulas look like but these will do the job:

Forename:

=MID(A1,FIND(", ",A1)+2,255)

Surname:

=LEFT(A1,FIND(", ",A1)-1)

Both, far from clunky!

You could use Data>Text to Columns but it's not automatic.

Biff

Thanks, that is the formula I used more or less, but thought it was a bit
clunky. Since you can split data into columns via the menu, I thought there
would be an equivalent function like SPLIT or TOKENISE or something
not to worry, thanks for the help

 

[Guest]

Lee,

Insert a column after your combined info (Surname and Forename), highlight
the column you want split out (ie if Surname and Forename are in Column "A",
highlight "A") then go to the "Data" menu and "Text to Columns". If your
data is consistant (ie John Smith and none without a Middle Initial) I would
suggest using the delimated by character, the character being the space. The
wizard will walk you through the rest.

Difficult1