Parsing Text

Alex O · Aug 27, 2010

I need a formula (in I2) that will look at H2 and parse out 5608 Ave K then J2 parse out Birmingham AL and K2 35208. This started out relatively easy enough....but the addresses that have Dr, Ave, Ln, etc are messing me up! Any ideas??

Thanks

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 248px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Billing Address</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">5608 Ave K Birmingham AL 35208</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">105 Shire Dr Pelham AL 35214</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold"> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">105 Shire Dr Pelham AL 35214</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">7735 Old Birmingham Hwy Cottondale AL 35453</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">109 Blackwell Ln Oakman AL 35579</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 51px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">2005 Old Montgomery Hwy Birmingham AL 35244</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

mikerickson · Aug 27, 2010

My suggestion, do it by hand.

Addresses (like names) has too many quirks to be dependably parsed by computer.
"St. Paul MN"

hiker95 · Aug 27, 2010

Alex O,

I agree with mikerickson.

But, for your sample data, before the macro:

Excel Workbook

H

I

J

K

1

Billing Address

2

5608 Ave K Birmingham AL 35208

3

105 Shire Dr Pelham AL 35214

4

5

105 Shire Dr Pelham AL 35214

6

7735 Old Birmingham Hwy Cottondale AL 35453

7

109 Blackwell Ln Oakman AL 35579

8

2005 Old Montgomery Hwy Birmingham AL 35244

9

Sheet1

After the macro:

Excel Workbook

H

I

J

K

1

Billing Address

2

5608 Ave K Birmingham AL 35208

5608 Ave K

Birmingham AL

35208

3

105 Shire Dr Pelham AL 35214

105 Shire Dr

Pelham AL

35214

4

5

105 Shire Dr Pelham AL 35214

105 Shire Dr

Pelham AL

35214

6

7735 Old Birmingham Hwy Cottondale AL 35453

7735 Old Birmingham Hwy

Cottondale AL

35453

7

109 Blackwell Ln Oakman AL 35579

109 Blackwell Ln

Oakman AL

35579

8

2005 Old Montgomery Hwy Birmingham AL 35244

2005 Old Montgomery Hwy

Birmingham AL

35244

9

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub SplitData()
' hiker95, 08/27/2010, ME491417
Dim c As Range, Sp, a As Long, b As Long, Hold As String
Application.ScreenUpdating = False
Columns("I:K").ClearContents
For Each c In Range("H2", Range("H" & Rows.Count).End(xlUp))
  If c <> "" Then
    Sp = Split(Trim(c), " ")
    a = UBound(Sp)
    Hold = ""
    For b = LBound(Sp) To UBound(Sp) - 3
      Hold = Hold & Sp(b) & " "
    Next b
    If Right(Hold, 1) = " " Then Hold = Left(Hold, Len(Hold) - 1)
    With c.Offset(, 1)
      .NumberFormat = "@"
      .Value = Hold
    End With
    c.Offset(, 2) = Sp(a - 2) & " " & Sp(a - 1)
    With c.Offset(, 3)
      .NumberFormat = "@"
      .Value = Sp(a)
    End With
  End If
Next c
Columns("I:K").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the "SplitData" macro.

Nalani · Aug 27, 2010

Mike,
If I may pick your brain for a moment:

I too have come up with this situation and reverted to doing it by hand. But I have the following for NAMES which I found on this Board:
Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 231px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>John R. Doe</TD><TD>John</TD><TD>R.</TD><TD>Doe</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>John Doe</TD><TD>John</TD><TD></TD><TD>Doe</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>John Randolph Doe</TD><TD>John</TD><TD>Randolph</TD><TD>Doe</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B4</TD><TD>=IF(ISERR(FIND(",",A4)),LEFT(A4,FIND(" ",A4)-1),IF(ISERR(FIND(" ",A4,FIND(" ",A4)+1)),RIGHT(A4,LEN(A4)-FIND(" ",A4)),MID(A4,FIND(" ",A4)+1,FIND(" ",A4,FIND(" ",A4)+1)-FIND(" ",A4)-1)))</TD></TR><TR><TD>C4</TD><TD>=IF(ISERR(FIND(",",A4)),IF(ISERR(FIND(" ",A4,FIND(" ",A4)+1)),"",MID(A4,FIND(" ",A4)+1,FIND(" ",A4,FIND(" ",A4)+1)-FIND(" ",A4)-1)),IF(ISERR(FIND(" ",A4,FIND(" ",A4)+1)),"",RIGHT(A4,LEN(A4)-FIND(" ",A4,FIND(" ",A4)+1))))</TD></TR><TR><TD>D4</TD><TD>=IF(ISERR(FIND(",",A4)),IF(ISERR(FIND(" ",A4,FIND(" ",A4)+1)),RIGHT(A4,LEN(A4)-FIND(" ",A4)),RIGHT(A4,LEN(A4)-FIND(" ",A4,FIND(" ",A4)+1))),LEFT(A4,FIND(",",A4)-1))</TD></TR><TR><TD>B5</TD><TD>=IF(ISERR(FIND(",",A5)),LEFT(A5,FIND(" ",A5)-1),IF(ISERR(FIND(" ",A5,FIND(" ",A5)+1)),RIGHT(A5,LEN(A5)-FIND(" ",A5)),MID(A5,FIND(" ",A5)+1,FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1)))</TD></TR><TR><TD>C5</TD><TD>=IF(ISERR(FIND(",",A5)),IF(ISERR(FIND(" ",A5,FIND(" ",A5)+1)),"",MID(A5,FIND(" ",A5)+1,FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1)),IF(ISERR(FIND(" ",A5,FIND(" ",A5)+1)),"",RIGHT(A5,LEN(A5)-FIND(" ",A5,FIND(" ",A5)+1))))</TD></TR><TR><TD>D5</TD><TD>=IF(ISERR(FIND(",",A5)),IF(ISERR(FIND(" ",A5,FIND(" ",A5)+1)),RIGHT(A5,LEN(A5)-FIND(" ",A5)),RIGHT(A5,LEN(A5)-FIND(" ",A5,FIND(" ",A5)+1))),LEFT(A5,FIND(",",A5)-1))</TD></TR><TR><TD>B6</TD><TD>=IF(ISERR(FIND(",",A6)),LEFT(A6,FIND(" ",A6)-1),IF(ISERR(FIND(" ",A6,FIND(" ",A6)+1)),RIGHT(A6,LEN(A6)-FIND(" ",A6)),MID(A6,FIND(" ",A6)+1,FIND(" ",A6,FIND(" ",A6)+1)-FIND(" ",A6)-1)))</TD></TR><TR><TD>C6</TD><TD>=IF(ISERR(FIND(",",A6)),IF(ISERR(FIND(" ",A6,FIND(" ",A6)+1)),"",MID(A6,FIND(" ",A6)+1,FIND(" ",A6,FIND(" ",A6)+1)-FIND(" ",A6)-1)),IF(ISERR(FIND(" ",A6,FIND(" ",A6)+1)),"",RIGHT(A6,LEN(A6)-FIND(" ",A6,FIND(" ",A6)+1))))</TD></TR><TR><TD>D6</TD><TD>=IF(ISERR(FIND(",",A6)),IF(ISERR(FIND(" ",A6,FIND(" ",A6)+1)),RIGHT(A6,LEN(A6)-FIND(" ",A6)),RIGHT(A6,LEN(A6)-FIND(" ",A6,FIND(" ",A6)+1))),LEFT(A6,FIND(",",A6)-1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Which works with my sample. Granted, I do not totally understand the Formula so I can't adjust for the OP.

When applied to the OP's data, I get the following:
Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 231px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">5608 Ave K Birmingham AL 35208</TD><TD style="TEXT-ALIGN: right">5608</TD><TD>Ave</TD><TD>K Birmingham AL 35208</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">105 Shire Dr Pelham AL 35214</TD><TD style="TEXT-ALIGN: right">105</TD><TD>Shire</TD><TD>Dr Pelham AL 35214</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(ISERR(FIND(",",A1)),LEFT(A1,FIND(" ",A1)-1),IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)))</TD></TR><TR><TD>C1</TD><TD>=IF(ISERR(FIND(",",A1)),IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),"",MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)),IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),"",RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))))</TD></TR><TR><TD>D1</TD><TD>=IF(ISERR(FIND(",",A1)),IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))),LEFT(A1,FIND(",",A1)-1))</TD></TR><TR><TD>B2</TD><TD>=IF(ISERR(FIND(",",A2)),LEFT(A2,FIND(" ",A2)-1),IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),RIGHT(A2,LEN(A2)-FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)))</TD></TR><TR><TD>C2</TD><TD>=IF(ISERR(FIND(",",A2)),IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),"",MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)),IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),"",RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2)+1))))</TD></TR><TR><TD>D2</TD><TD>=IF(ISERR(FIND(",",A2)),IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),RIGHT(A2,LEN(A2)-FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2)+1))),LEFT(A2,FIND(",",A2)-1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Question is - Could the formula be adjusted to give the OP's requirement?

Not only for the OP, but for my learning curve.

Alex O · Aug 27, 2010

I'm was initially thinking mikerickson was right (I was at my wits-end!)...but both of you (hiker95 and nalani) have me feeling a bit more optomistic! I'm trying to figure out certain aspects of these formulas that aren't really clear....

Alex O · Aug 28, 2010

hiker95 Your macro delivered exactly what I needed...THANKS!

GTO · Aug 28, 2010

Greetings Alex,

Just really a couple of "thinking out loud" questions.

Your sample data, while minimal, shows a very consistent pattern, being: A street address, followed by a city/town name, followed by a two-alpha character state code, followed by a zip code (examples with only 5 digits and no hyphen).

Can we consistently count on this pattern? Only if we can...

Certainly if this is a one time deal, I fully agree with Mike. If its a one-time deal, I would do it by hand.
If its some new list every day/week/month, then I was wondering as to the scope/locale of the destination addresses. For instance, will all the addresses be in Alabama, or within several known states?

The reason I was curious, is that I was thinking that as the street address section is likely to be the goofiest part - if we know we can count on a limited area of states/cities, then maybe build a list of all these on another sheet, and use this.

Mark

Alex O · Aug 28, 2010

It's a new list every month. The cities constantly change, but for the most part they will always be in AL.

GTO · Aug 28, 2010

Alex O said:
It's a new list every month. The cities constantly change, but for the most part they will always be in AL.

Okay, on the occassions that they are not in Alabama, will they be in one of these several united states? I am asking for specificity, as I want to know if we can count on the 2-alpha character state code. See, as long as the city has only one word in its name, Hiker's code would work great. The goofy part is when the city's name is made up of two or more words, like Las Vegas, Baton Rouge, or

Excel Workbook

H

I

J

K

8

2005 Old Montgomery Hwy Birmingham AL 35244

2005 Old Montgomery Hwy

Birmingham AL

35244

9

2005 Old Montgomery Hwy Bayou La Batre AL 35244

2005 Old Montgomery Hwy Bayou La

Batre AL

35244

DataSheet

Mark

Alex O · Aug 28, 2010

They will always be in AL...

Parsing Text

Alex O

Active Member

mikerickson

MrExcel MVP

hiker95

Well-known Member

Nalani

Well-known Member

Alex O

Active Member

Alex O

Active Member

GTO

MrExcel MVP

Alex O

Active Member

GTO

MrExcel MVP

Alex O

Active Member

Similar threads

Forum statistics

Share this page

Parsing Text

Active Member

MrExcel MVP

Well-known Member

Well-known Member

Active Member

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active 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