View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_] Shane Devenshire[_2_] is offline
external usenet poster
 
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,