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,