Extract street number (foreign address)
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, |
Extract street number (foreign address)
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)? |
Extract street number (foreign address)
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, |
Extract street number (foreign address)
=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, |
Extract street number (foreign address)
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, |
Extract street number (foreign address)
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 |
Extract street number (foreign address)
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")))) |
Extract street number (foreign address)
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, |
Extract street number (foreign address)
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)? |
All times are GMT +1. The time now is 10:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com