Help splitting one cell into multiple cells

Stevethetaxman · May 10, 2006

I want to split out one cell into more than one.

For example, in cell A1, I have:

John Smith 123 Main St. New York NY 12345

I want first name, last name, street address, town, state & zip in cells B1 through G1.

Any help would be appreciated.

Thanks

Smitty · May 10, 2006

What's your data source?

Because it gets complicated with Middle Names/Prefixes/Suffixes, Address/Suites & 2-word cities/states...etc.

Smitty

joose · May 10, 2006

Select the cell
Go to Data -> Text to Columns
Follow the instruction in the dialog box

JerryGiese · May 10, 2006

This is as close as I can get you, but you will have to put the number of words in the city in Column H

B1=

Code:

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

C1=

Code:

=MID(TRIM(A1),FIND(" ",TRIM(A1))+1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)-FIND(" ",TRIM(A1))-1)

D1=

Code:

=MID(TRIM(A1),FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)+1,FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))-1-H1))-FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)-1)

E1=

Code:

=MID(TRIM(A1),FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))-1-H1))+1,FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))-1))-FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))-1-H1))-1)

F1=

Code:

=MID(TRIM(A1),FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))-1))+1,FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))))-FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1))-1))-1)

G1=

Code:

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND("  ",SUBSTITUTE(TRIM(A1)," ","  ",LEN(SUBSTITUTE(A1," ","  "))-LEN(TRIM(A1)))))

H1=
The number of words in the city. For example enter 2 for New York

Forcados · May 10, 2006

Here is a little routine that I use to parse rows of data, but it is based on a space modifier - thus "John" would be in B1, "Smith" in B2, etc.

If you had some data uniformity in terms of the number of spaces per data element, you could put in some conditional statements to skip say, two spaces, etc.

It's not quite what you want, but moving in the right direction and perhaps there is easier code, but this works for me...

Note it asks how many rows to parse, and starts at A1 down to An (where n is the number of rows)

Public Sub Parser()

Dim inString As String
Dim subStr As String

Dim rowsTotal As Integer
Dim rowCtr As Integer
Dim colCtr As Integer

Dim length As Integer
Dim x, y, z As Integer

rowsTotal = Application.InputBox("Enter number of rows to parse")

For rowCtr = 1 To rowsTotal

colCtr = 2

y = 1
z = 1
inString = Cells(rowCtr, 1)
length = Len(inString)
subStr = Mid(inString, y, z)

For x = 2 To length

If Asc(Mid(inString, x, 1)) = 32 Then

' Here we have a space
Cells(rowCtr, colCtr) = subStr
subStr = ""
y = x + 1
' z is the length of the substring, need to reset to 1
z = 1
colCtr = colCtr + 1

Else

' Here we have another character
' so add it to the substring
z = z + 1
subStr = Mid(inString, y, z)

End If

Next x

' need to write out the last one
Cells(rowCtr, colCtr) = subStr

Next rowCtr

End Sub

Help splitting one cell into multiple cells

Stevethetaxman

New Member

Excel Facts

Smitty

Legend

joose

Active Member

JerryGiese

Active Member

Forcados

New Member

Similar threads

Forum statistics

Share this page

Help splitting one cell into multiple cells

Stevethetaxman

New Member

Excel Facts

Smitty

Legend

joose

Active Member

JerryGiese

Active Member

Forcados

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock