How can I remove all non numberic characters from a field in Excel?

Question

For example I have three cell values:

ABC123
456DEF
AB34EF

I want to write a formula that will return:
123
456
34

Any tips or advice on how to dow that in Excel???

Thanks!

Answer 1

Here's a simple Macro:

Sub RemoveLetters()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Answer 2

Well, Excel does not come with a "simple" formula or function to do this.

So, the way I see it, you have few choices:

CHOICE 1) Buy an add-in that someone else developed for this express purpose, such as "itEXTRACTNUM", a formula function that would do exactly what you want...and is included in this add-in:
http://precisioncalc.com/it/itEXTRACTNUM.html
There is a free version that is a pain to work with, or you could spend $12 and avoid the hassles.

CHOICE 2) Use the following complicated mess I just developed (in attempt to achieve what you want):

First: Put all the cell values you want to convert in Column A, starting with Row 2 and going down.

Second: Put a series of numbers in the first Row, starting with 1 in B1 and extending to 6 to G1 (or further, to the total number of characters for each unconverted string).

Like this: 123456
ABC123
456DEF
AB34EF
ABC78F
A158EF
...


Third: Copy and past the following formula into every cell within the table (From B2 to G2 and all the way down your list):

=IF( AND( CODE( MID($A2,B$1,1))>47, CODE( MID($A2,B$1,1))<59), CODE( MID($A2,B$1,1))-48,"")

(Make sure this is all one continuous line, of course)

Fourth (and finally): in cell H2 (which will yield you your desired output of 123, 456, 34, etc...), enter the following formula:

= VALUE( CONCATENATE( B2,C2,D2,E2,F2,G2))

Copy and paste this down the length of the column for your list, and voila, there you go! (To clean up your spreadsheet, you can simply HIDE columns B through G).

~~~~~~~
Underlying thought for how this mess works:
MID extracts each individual character of your original string, at the position designated by your series in the first row.

CODE returns a numeric code for that character (ANSI Character set for Windows, if you are using a mac, you'll have to change the IF statement range)

IF statement takes all CODE numerics from 48 to 58 (the numerics for the single digits numbers from 0 to 9, respectively), and subtracts 48...to yield the correct number. If the code numeric is not between 48 and 58 (eveything other than a single digit number), then it just puts a blank, "").

The H Column then CONCATENATES the results (blanks disappear and your numbers remain), and then converts the text to a number (for calculation purposes) using VALUE.
~~~~~~

CHOICE 3) Use Visual Basic to do something similar to what I did above.

CHOICE 4) Wait for someone more knowledgeable than I to come up with an easier and more manageable solution!

Hope this helps!

Grace and peace,
yachadhoo
;)