Parsing Text

Alex O

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
My suggestion, do it by hand.

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

I agree with mikerickson.


But, for your sample data, before the macro:


Excel Workbook
HIJK
1Billing Address
25608 Ave K Birmingham AL 35208
3105 Shire Dr Pelham AL 35214
4
5105 Shire Dr Pelham AL 35214
67735 Old Birmingham Hwy Cottondale AL 35453
7109 Blackwell Ln Oakman AL 35579
82005 Old Montgomery Hwy Birmingham AL 35244
9
Sheet1





After the macro:


Excel Workbook
HIJK
1Billing Address
25608 Ave K Birmingham AL 352085608 Ave KBirmingham AL35208
3105 Shire Dr Pelham AL 35214105 Shire DrPelham AL35214
4
5105 Shire Dr Pelham AL 35214105 Shire DrPelham AL35214
67735 Old Birmingham Hwy Cottondale AL 354537735 Old Birmingham HwyCottondale AL35453
7109 Blackwell Ln Oakman AL 35579109 Blackwell LnOakman AL35579
82005 Old Montgomery Hwy Birmingham AL 352442005 Old Montgomery HwyBirmingham AL35244
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.
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
hiker95 Your macro delivered exactly what I needed...THANKS!
 
Upvote 0
Greetings Alex,

Just really a couple of "thinking out loud" questions.
  1. 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...
  1. 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.
  2. 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
 
Upvote 0
It's a new list every month. The cities constantly change, but for the most part they will always be in AL.
 
Upvote 0
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.
Click to expand...

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
HIJK
82005 Old Montgomery Hwy Birmingham AL 352442005 Old Montgomery HwyBirmingham AL35244
92005 Old Montgomery Hwy Bayou La Batre AL 352442005 Old Montgomery Hwy Bayou LaBatre AL35244
DataSheet



Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
421
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
345
esben
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
490
John_w
J
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A

Forum statistics

Threads
1,214,630
Messages
6,120,634
Members
448,973
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top