Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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
Asked:
monster53
  • 2
  • 2
1 Solution
 
Harry LeeCommented:
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)

Open in new window


After paste in the formula in edit mode, enter using Ctrl-Shift-Enter
0
 
monster53Author 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
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Harry LeeCommented:
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
 
monster53Author Commented:
This is EXCELLENT !!!!!!!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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