Discussion on:

The fastest way to parse text in Excel

This is a great tip. I have had to do this several times with first names and last names in one column.

Just for kicks, the way I figured out how to do it was to use the "Search and Replace" feature. It takes a few more steps than the "Text To"feature, but might come in handy some day (it has for me). I just copy the column and then paste it next to the original, so I have two columns side by side, then select the first column, and then Search (ctrl-h) for the string "* " (that's asterisk + space) and replace it with nothing. Then select the second column and search for " *" (space + asterisk) and replace it with nothing.

Chad

Both methods need to be used with caution if the text has multiple spaces such as Harold Van Klees where the first name is Harold and the Last name is Van Klees. I encountered this type of problem in Holland where everyone has a double name.

A great tip, indeed!

If the co-worker in the example needed to add the different types (fax, e-mail, etc.) into subtotals, she probably used the SUMIF function, i.e. in E1-En she wrote the different types and in F1-Fn the function =SUMIF($B$1:$B$20,E1,$A$1:$A$20). In this way she gets the number of faxes in F1, e-mails in F2, and so on.
NB! You need to write it only once, after that you just copy it to the rest of the cells.

I found this "old fashioned way" more useful when I want to separate Surname and (two or more)Initials into two columns. With "Text To" feature, each Initial occupies a column which was not required.

I don't understand your old-fashioned way.. are you saying you can do search and replace with wildcards? Is that a feature of a newer version of Excel? I have Excel 2002...

It didn't work when I first tried it for some reason, but lo and behold... How did I never know you could use wildcards?

Thanks for this wonderful, effortless, time-saving tip.

REW

Took the words right out of my mouth.

If you need to split a column containing test with more than one space, like compound names, try a macro like the following one.

'Declare variables
Dim strColumn1 As String
Dim strColumn2 As String
Dim strText As String
Dim intStartRow As Integer

'Initialize variables
strColumn1 = Mid(ActiveWindow.RangeSelection.Address, 2, 1)
strColumn2 = Mid(Application.ConvertFormula(Formula:="R" & Trim(Str(ActiveWindow.RangeSelection.Row)) & "C" & Trim(Str(ActiveWindow.RangeSelection.Column + 1)), fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1), 2, 1)
intStartRow = ActiveWindow.RangeSelection.Row

'Insert a new column to the right of the active one
ActiveCell.EntireColumn.Insert
'While there is text to split, do the loop
Do While Sheets(ActiveSheet.Name).Range(strColumn2 & Trim(Str(ActiveCell.Row))).Text ""
'Load the text into the text variable
strText = Sheets(ActiveSheet.Name).Range(strColumn2 & Trim(Str(ActiveCell.Row))).Text
'Write everything afterthe first space to the new column
'If there is no space in the text, write an empty string
Sheets(ActiveSheet.Name).Range(strColumn2 & Trim(Str(ActiveCell.Row))).Value = IIf(InStr(1, strText, " ") = 0, "", Right(strText, Len(strText) - InStr(1, strText, " ")))
'Write everything before the first space to the active column
'If there is no space in the text, write the entire string
Sheets(ActiveSheet.Name).Range(strColumn1 & Trim(Str(ActiveCell.Row))).Value = IIf(InStr(1, strText, " ") = 0, strText, Left(strText, InStr(1, strText, " ")))
'Advance to the next row
Sheets(ActiveSheet.Name).Range(strColumn1 & Trim(Str(ActiveCell.Row + 1))).Select
Loop
'Return to where we started
Sheets(ActiveSheet.Name).Range(strColumn1 & Trim(Str(intStartRow))).Select

There's probably a simpler way to do this, but I haven't taken the time to look for one.

John

The figures in the article do not show up. How can I see what they are? I've exited and returned to the article with no success.

Please help me in knowing what needs to be done if a blank space does not exist between the number and the text.

This works fine if the column is completely filled with this type of data. But what if the data bellow has a different type?
Try using the "Text" functions wich will allow you not only to split text but also concatenate and take only the letters you whant, plus others. In a personalized way and only to the text you need.

Excellent. Added to personal cook book!

Is there some way to deposit the different results (i.e., the value you just parsed and the remainder of the string) into A) Cells that are on a different page and B) cells that are not necessarily in adjacent columns?

My rows has different numbers of columns, so after Text-to-Columns function, they are mis-aligned like this (since it starts from the left)

5 4 3 2 1
4 3 2 1
2 1

How do I force it to start from the right to have the following result?

5 4 3 2 1
4 3 2 1
2 1

Thanks.

LOL - the fastest way is to wrap the VBA function split :-P None of that reinventing the wheel stuff! Geez people...

Function XLSplit(Blahblah As String, sDLM As String, Optional Ret As Integer = 0)
XLSplit = Split(Blahblah, sDLM)(Ret)
End Function

Cheerio! gene.boo@gmail.com

Thanks - Great help

Ok, I track my domains in Excel, so how do I parse out .com/.net/.whatever and tell how many dot coms/dot nets I have?
The spreadsheet has one column of domains (no www, just whatever_domain_name.com).
Thanks!

Hi,you can use Realcool import manger to do this job.It can be used as office addin.
It can import any delimited text files.Support multi delimiters .You can download the demo from http://www.realcoolsoft.com to test. It's very useful and powerful.

I am trying to parse street addresses in order to change abbreviated Street Types (Rd, St, Dr) into the full word (Road, Street, Drive, etc).

Addresses have up to 4 components: Street Number, Street Name, Street Type, Direction (Example: 109 Green Street SW).

Now, not all Street Names are consistent in length. In other words, I could have a Street Name with one word (Green), or a Street Name with up to 5 words (Mitchell Joe Martin Reams Memorial - Street Type in this case is Drive--The entire name including Name and Type would be Mitchel Joe Martin Reams Memorial Drive NE)

Since the parsing separates all of the components of the address into different columns, I have a problem with how to pull out the Street Types and the Direction.

Once those are pulled out, I have formulas that work for changing the abbreviated Street Types to the full version and for finding Directions (if they exist).

I just don't know how to pull those two things out because the Type or Direction could fall into different columns. Any suggestions here? No VB, don't know how to use it. Have to do this exclusively with Excel. Thanks so much!