Extract street number (foreign address)

[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,

 

[Shane Devenshire]

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

 

[Teethless mama]

=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"))))

 

[Satti Charvak]

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

 

[Ron Rosenfeld]

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,

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

 

[Sei]

Thank you, this works well.

 

[Sei]

THank you. I will try this next.

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

 

[Sei]

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