Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 13th 08, 12:56 AM posted to microsoft.public.excel.worksheet.functions
Sei Sei is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default 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,



  #2   Report Post  
Old November 13th 08, 01:49 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 915
Default 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)?
  #3   Report Post  
Old November 13th 08, 04:05 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3,346
Default 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,


  #4   Report Post  
Old November 13th 08, 04:23 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,718
Default 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,


  #5   Report Post  
Old November 13th 08, 11:43 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 53
Default 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,




  #6   Report Post  
Old November 13th 08, 04:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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
  #7   Report Post  
Old November 13th 08, 08:49 PM posted to microsoft.public.excel.worksheet.functions
Sei Sei is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default 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"))))


  #8   Report Post  
Old November 13th 08, 08:52 PM posted to microsoft.public.excel.worksheet.functions
Sei Sei is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default 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,


  #9   Report Post  
Old November 13th 08, 09:28 PM posted to microsoft.public.excel.worksheet.functions
Sei Sei is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default 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)?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separate address numbers from street name Walt Moeller Excel Worksheet Functions 4 June 4th 08 04:29 PM
Convert street address to zip code SJC Excel Worksheet Functions 1 April 25th 08 08:50 PM
arrange excel by street address instead of alphabetically by name Ray Excel Worksheet Functions 9 June 13th 07 03:33 PM
outlook 3 line street address joe smith Excel Discussion (Misc queries) 2 November 22nd 05 02:32 PM
How do I sort a column of street number/street name by the stree. JHoleman1 Excel Worksheet Functions 3 January 8th 05 12:18 AM


All times are GMT +1. The time now is 07:00 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017