Extract street number (foreign address)

  • Thread starter Thread starter Sei
  • Start date Start date
S

Sei

I'm trying to separate the street number and street name but when with a
foreign address I'm having a hard time especially if the number is right in
the middle such as:

Av 47 1330 Col Hidalgo
Av 55 339 Co. Hidalgo
Av Aguas Calientas 1332 Col Puebl
Av Baja De Los Angeles 1283
Av Cesar Prieto 3139 Calocio
Av Colima 3033 Col Oscar G

They are not always on the same word position so I'm wondering is there a
way to extract the number from a cell?? I found a formula in Microsoft usin
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

and entered is as an array (CTRL+SHIFT+KEY) it didnt work at first so I had
to remove the spaces. Unfortunately, after I removed the spaces, it worked on
some cells but not on all - the rest would say N/A).

RESULT AFTER REMOVING SPACES:
471330
55339
#N/A
#N/A
#N/A
3

Any help is appreciated. Thank you,
 
Hi,

You can not parce data that is inconsistent! This is the meaning of the old
saying "Garbage in, garbage out"

Good luck,

Shane Devenshire
 
=LOOKUP(99^99,--MID(SUBSTITUTE(A1,"
",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,"
","")&"0123456789")),ROW(INDIRECT("1:256"))))
 
I have a macro for you:

Private Sub cleanData()
Dim i As Integer
Dim j As Integer
Dim myStr As String
i = 1

While Len(Trim(Cells(i, 1).Value)) > 0
For j = 1 To Len(Trim(Cells(i, 1).Value))
If Asc(Mid(Cells(i, 1), j, 1)) > 47 And Asc(Mid(Cells(i, 1), j,
1)) < 58 Then
myStr = myStr & Mid(Cells(i, 1), j, 1)
End If
Next
Cells(i, 2).Value = myStr
i = i + 1
myStr = ""
Wend
End Sub
 
I'm trying to separate the street number and street name but when with a
foreign address I'm having a hard time especially if the number is right in
the middle such as:

Av 47 1330 Col Hidalgo
Av 55 339 Co. Hidalgo
Av Aguas Calientas 1332 Col Puebl
Av Baja De Los Angeles 1283
Av Cesar Prieto 3139 Calocio
Av Colima 3033 Col Oscar G

They are not always on the same word position so I'm wondering is there a
way to extract the number from a cell?? I found a formula in Microsoft usin
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

and entered is as an array (CTRL+SHIFT+KEY) it didnt work at first so I had
to remove the spaces. Unfortunately, after I removed the spaces, it worked on
some cells but not on all - the rest would say N/A).

RESULT AFTER REMOVING SPACES:
471330
55339
#N/A
#N/A
#N/A
3

Any help is appreciated. Thank you,
Click to expand...

How can we tell, from examining the string, which of the numbers is a "street
number"?

Once you can state an unambiguous "rule" for making that determination, it
would be trivial to extract it.
--ron
 
THank you. I will try this next.

Satti Charvak said:
I have a macro for you:

Private Sub cleanData()
Dim i As Integer
Dim j As Integer
Dim myStr As String
i = 1

While Len(Trim(Cells(i, 1).Value)) > 0
For j = 1 To Len(Trim(Cells(i, 1).Value))
If Asc(Mid(Cells(i, 1), j, 1)) > 47 And Asc(Mid(Cells(i, 1), j,
1)) < 58 Then
myStr = myStr & Mid(Cells(i, 1), j, 1)
End If
Next
Cells(i, 2).Value = myStr
i = i + 1
myStr = ""
Wend
End Sub


--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India
Click to expand...
 
You're right, there is no easy way. I managed to do it using Teethless Mama's
recommendation but it also involved a lot of find, replace, and sort.

Thanks for the reply
 
Back
Top