• Status: Solved
• Priority: Medium
• Security: Public
• Views: 289

# Copying only the numbers from a cell

I have a large column that has a combination of letters and numbers. They are not consistent , some has letters, and some has charterers like blank, (, or &.

Is there a way for me to copy to another column with just the numbers and have them stacked next to each other,

So for example, if the cell contains (#125-54A24) it will copy 1255424
So for example, if the cell contains -AA   23-   56267  it will copy 2356267
0
monster53
• 2
• 2
1 Solution

Commented:
Use array formula,

``````=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)
``````

After paste in the formula in edit mode, enter using Ctrl-Shift-Enter
0

Author Commented:
I assume that i need to change the A1 to the appropriate cell.
I did that, however it is only returning the first digit in the cell, if the first digit is a number. If it is not, it is returning a 0.
0

Older than dirtCommented:
Put this UDF in a module and then use it as you would any formula. For example =(A2)

``````Function GetNumber(r As Range) As Long
Dim lngIndex As Long
Dim strTemp As String

For lngIndex = 1 To Len(r.Value)
If IsNumeric(Mid(r.Value, lngIndex, 1)) Then
strTemp = strTemp & Mid(r.Value, lngIndex, 1)
End If
Next

GetNumber = CLng(strTemp)
End Function
``````
0

Commented:
monster53,

Please look at the attached sample file. It will show you how the cell references work on the formula.

Did you remember to enter using Ctrl-Shift-Enter?
Keep-Only-Numeric-Character.xlsx
0

Author Commented:
This is EXCELLENT !!!!!!!
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.