ExcelBanter - Extract only numbers from an alphanumeric field in Excel?

Hi Brian,

You could use this UDF (User defined Function):

' ================================================== ===========================

Function StripTxt(a As String) As String

' Niek Otten, March 22 2006

' Strips all non-numeric characters from a string, but leaves any decimal separator

' Returns a string, not a number!

' If you need a number, use =Value(StripTxt(...))

Dim i As Long

Dim b As String

For i = 1 To Len(a)

b = Mid$(a, i, 1)

If ((Asc(b) 47 And Asc(b) < 58) Or b = Application.DecimalSeparator) Then StripTxt = StripTxt + b

Next i

End Function

' ================================================== ===========================

If you don't know (yet) how to implement a UDF:

================================================

Pasting a User Defined Function (UDF)

Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these steps:

Select all the text of the function. CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut
for Copy.

Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).

From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then press
CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.

Press ALT+F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)

================================================

--
Kind regards,

Niek Otten

"Brian" wrote in message ...
I am trying to extract only the numbers from an alphanumeric field. The
numbers are not consistently in the same spot. Example: abc123, 123abc or
a123bc... I want a returned value of "123".

Thank you