I need to extract numeric values from a cell with alphanumeric or having other character to another cell.
There is no specific format of the cell.
I have the similar cells in thousands.
For eg:
A1: ab2hgi;234kjf8r
Result:
22348
Printable View
I need to extract numeric values from a cell with alphanumeric or having other character to another cell.
There is no specific format of the cell.
I have the similar cells in thousands.
For eg:
A1: ab2hgi;234kjf8r
Result:
22348
Have a look here
Code for Trim Non-numeric values..
Here is a macro that you can try...
The only thing you need to do is change the three constants (the Const statements) to match your actual layout. You can set the FirstOutputCell constant to your first data cell if you want to overwrite your original data with the numbers contained within them.Code:Sub ReduceToNumbersOnly()
Dim X As Long, Z As Long, LastRow As Long, Strng As String, vArr As Variant
Const FirstOutputCell As String = "B2"
Const DataColumn As String = "A"
Const StartRow As Long = 2
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
vArr = Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)
For X = 1 To UBound(vArr)
Strng = ""
For Z = 1 To Len(vArr(X, 1))
If Mid(vArr(X, 1), Z, 1) Like "#" Then Strng = Strng & Mid(vArr(X, 1), Z, 1)
Next
vArr(X, 1) = Strng
Next
Range(FirstOutputCell).Resize(UBound(vArr)) = vArr
End Sub