Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
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 |
#2
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
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 DataText to Columns but it's not automatic. Biff "Lee Harris" wrote in message ... 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 |
#3
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
Try selecting your column, and use Data--Text to columns.
http://www.officearticles.com/excel/...soft_excel.htm (About halfway down the page) ************ Anne Troy www.OfficeArticles.com "Lee Harris" wrote in message ... 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 |
#4
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
On the Data menu, click Text to Columns, select the radio button marked
"Delimited" select "Next" and then select the "Comma" check box. Hit finish "Lee Harris" wrote: 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 |
#5
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
On Fri, 21 Oct 2005 21:45:01 GMT, "Lee Harris" wrote:
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 |
#6
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
"Biff" wrote in message ... 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 DataText 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 |
#7
|
|||
|
|||
Text Splitting easier? (Tokeniser?)
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 "Lee Harris" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resizing row height to dynamically fit height of text box | Excel Discussion (Misc queries) | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) |