remove non-numeric characters from cells

[Guest]

I have a column with data that may contain both text and numbers. Is there a
macro that extracts only the numbers and place them in the adjacent column?
Example:
Column A Column B
Row 1 A12 12
Row 2 BB121 121
Row 3 1E 1
Row 4 13AA 13
Row 5 66 66

Your help is much appreciated! Thanks!

 

[Guest]

I don't know if there's a built-in function but you can do it yourself like:

Public Function Digits(sVal As String)
'
Dim Digit As String
Dim i As Long
'
For i = 1 To Len(sVal)
Digit = Mid(sVal, i, 1)
If Digit >= "0" And Digit <= "9" Then Digits = Digits & Digit
Next i
'
End Function

and in A1 use this formula:

=IF(A1="","",digits(A1))

(and copy down the column)

 

[Guest]

Hi,

I copied this from a post a few weeks ago, it will work for the examples
posted but goes strange if you start trying to extract numbers from strings
such as

11BB11

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Mike