Parsing Address Information 1

[lizarde03]

I am scanning in addresses and trying to create a db with these addresses. Once i scan them and clean them up a little they are in the following format...

John Doe
123 Main Street
Cleveland, OH 12345

Jane Doe
234 Main Street
Akron, OH 23456

Jen Doe
345 Main
Cleveland, OH 23453

I have a list of several hundred. Is there a way to move the info in to 3 columns without manually doing it? Thanks for the help!

 

[kphu]

what type of database are you using?

 

[lizarde03]

it will eventually be an access database.

 

[kphu]

What application are you currently scanning the data into?

 

[lizarde03]

Excel

 

[kphu]

assuming that the data is in column "A"

i 'm not sure how much you know about macro's and vb code but, you can create a macro using this code.

Before running this macro scan all of your data into the excel sheet.

Sub formataddresses()
Application.ScreenUpdating = False
Dim r As Range
Dim i As Integer
Dim n As String
Set r = Intersect(ActiveSheet.UsedRange, Range("A1:a65536"))

For i = r.Count To 1 Step -1
r.Cells(i).Select
If Right(r.Cells(i).Value, 1) Like "#" Then
n = "3"
Else
End If
If Left(r.Cells(i).Value, 1) Like "#" Then
n = "2"
Else
End If

r.Cells(i).Select
Select Case n
Case 2
ActiveCell.Cut
r.Cells(i).Offset(-1, 1).Select
ActiveSheet.Paste
n = ""
Case 3
ActiveCell.Cut
r.Cells(i).Offset(-2, 2).Select
ActiveSheet.Paste
n = ""

Case Else
End Select
Next i
Application.ScreenUpdating = True
End Sub

Let me know if you have any problems.

Ken

 

[lizarde03]

Wow...I actually have it working!!! There is one small issue that I'm seeing.. There are a few addresses that have 4 lines of addresses because of a business name. Currently, it is leaving that businessname on a separate row. Is there anyway to fix that?

Thanks!

 

[kphu]

i wrote the code based on the example you gave me so I didn't take that into account.

If you wanna give me a better sample I can try and edit the code.

ken

 

[AlaskanDad]

Here's a non-macro solution.

First, I assumed you had data that looked like this in Column A:
John Doe
123 Main Street
Cleveland, OH 12345

Jane Doe
234 Main Street
Akron, OH 23456

Jen Doe
ABC company
345 Main
Cleveland, OH 23453

Some are three lines, some are four. All groups are separated by a blank row.

Here are the formulas for you to copy and paste then drag down:
B1: =IF((($A1<>"")*($A4<>"")*($A5="")),$A1,IF((($A1<>"")*($A4="")*($A5<>"")),$A1,""))
C1: =IF((($A1<>"")*($A4<>"")*($A5="")),$A2,IF((($A1<>"")*($A4="")*($A5<>"")),"",""))
D1: =IF((($A1<>"")*($A4<>"")*($A5="")),$A3,IF((($A1<>"")*($A4="")*($A5<>"")),$A2,""))
E1: =IF((($A1<>"")*($A4<>"")*($A5="")),$A4,IF((($A1<>"")*($A4="")*($A5<>"")),$A3,""))

Once copied to all rows of your data, just copy, paste special (values) to get rid of the formulas. Next, filter out all of the empty rows and you're set!

Hope it helps.
- Rob

 

[xlbo]

kphu - for ref - there is a

code

tag that can be used for code examples rather than the

quote

tag

lizarde03 - if you have irregular data, this is going to be hard - you need an identifier to show where 1 set of data starts and another stops. If you do not have that (a blank row would suffice) and you have irregular rows of data per customer than there is no logic that can be used I'm afraid

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AlaskanDad]

Just for fun...

xlbo wrote:

lizarde03 - if you have irregular data, this is going to be hard - you need an identifier to show where 1 set of data starts and another stops. If you do not have that (a blank row would suffice) and you have irregular rows of data per customer than there is no logic that can be used I'm afraid

I know that getting data in this kind of shape is very common. What if there is no blank row in between each set? I propose that there is logic in there that you can use to break it apart into the appropriate rows.

I found an answer. Any others?

- Rob

 

[AlaskanDad]

This is what I got the data to do:

This ...


     A              

John Doe            
123 Main Street				
Cleveland, OH 12345				
Jane Doe            
234 Main Street
Akron, OH 23456
Jen Doe             
ABC company
345 Main
Cleveland, OH 23453

... becomes this



   B         C          D                  E

John Doe             123 Main Street Cleveland, OH 12345


Jane Doe             234 Main Street Akron, OH 23456


Jen Doe  ABC company 345 Main        Cleveland, OH 23453

It was a good brain workout for a Friday!

- Rob

 

[kphu]

I don't wanna write the code out but there is a logic to your sample:

A

John Doe
123 Main Street
Cleveland, OH 12345
Jane Doe
234 Main Street
Akron, OH 23456
Jen Doe
ABC company
345 Main
Cleveland, OH 23453

The logic would be similar to the orignal code I posted. Which is to look at the value in the current field. By using if the last character is a number then you know its state, zip which tells you the position its in.

From this you can then count how many rows above it before you hit the next cell that contains state, zip data. Using this positioning you can determine how to code your data to place the data into the correct placing. To make it a little better then the format you have above:

... becomes this



B C D E

John Doe 123 Main Street Cleveland, OH 12345


Jane Doe 234 Main Street Akron, OH 23456


Jen Doe ABC company 345 Main Cleveland, OH 23453

You should really put the company data into column E and shift the street and state zip data 1 colum over so that it looks like this.

   B         C          D                     E

John Doe 123 Main Street Cleveland, OH 12345


Jane Doe 234 Main Street Akron, OH 23456


Jen Doe  345 Main        Cleveland, OH 23453  ABC company

 

[AlaskanDad]

Your logic breaks down if there is:
124 Main Street Suite #400

Also, how would you "count" above until you reach the next zip code line? How would you do that for the first address when there isn't anything above?

(all in good fun)

 

[kphu]

The logic above was written with regards to the sample that was given.

Will have to go back to the drawing board with that sample. : )

As for the count. You can do a do until right(r.cells(i).value,1) like "#"
the loop just tells it to move the current cell up one until it matches. you'll be starting from the bottom of course.
then set an integer to increase for each loop
i = i + 1
loop

For some error control.
stick in an if statement.
if activecell.address = "A1" then
else
stick the loop code here.

Abstract thinking is always fun.

 

[kphu]

oh just came up with a control to make the code work.

you can check the cell value if the first digit is a # and the last chracter is # if it is then continue the loop.

 

[AlaskanDad]

Jim Doe
The Hartford Building, Suite #400
New York, NY 12012

Kills that logic.

The only pattern I see is:
", NY 12012"
Comma space something something space something something something something something

You'd have to assume you'd get a comma, a space, the two letter state, another space, then five digits.

I found my solution using just functions in the cells. No looping in VB at all.

 

[kphu]

haha

your just trying to beat me down huh?

Well my next suggestion was basically similar to yours.

The only thing is that you'd have to also take into account of zip codes like 12012-4123

Ok enough of this thinking.

its TGIF!!!