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,

Sei wrote:
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

Looking at your sample data I think the first question you you ask
yourself is "how do I know what a street number is?" The first two rows
have two numbers in them, which are probably a "street name" and "street
number". Given this, I suppose we/you could work something out that
ignores numbers following common street prefixes like "Av".

But what if you were confronted with data like

Av 47 1330 Col Hidalgo Ste 10
or
Av 47 1330A Col Hidalgo
or
Av 47 1330-1/2 Col Hidalgo

?

Just food for thought... Parsing data like this is seldom a
straightforward task, as there is seldom a typical solution.

As you take a long look at the data in front of you, and think about
what data may come, are there any patterns you can isolate and describe
to make identifying a "street number" a sure bet (or pretty close)?

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


"Sei" wrote:

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,

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


"Sei" wrote:

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,

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" wrote:

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,

On Wed, 12 Nov 2008 16:56:04 -0800, Sei wrote:

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

Thank you, this works well.

"Teethless mama" wrote:

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

THank you. I will try this next.

"Satti Charvak" wrote:

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" wrote:

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,

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

"smartin" wrote:

Sei wrote:
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

Looking at your sample data I think the first question you you ask
yourself is "how do I know what a street number is?" The first two rows
have two numbers in them, which are probably a "street name" and "street
number". Given this, I suppose we/you could work something out that
ignores numbers following common street prefixes like "Av".

But what if you were confronted with data like

Av 47 1330 Col Hidalgo Ste 10
or
Av 47 1330A Col Hidalgo
or
Av 47 1330-1/2 Col Hidalgo

?

Just food for thought... Parsing data like this is seldom a
straightforward task, as there is seldom a typical solution.

As you take a long look at the data in front of you, and think about
what data may come, are there any patterns you can isolate and describe
to make identifying a "street number" a sure bet (or pretty close)?