remove alpha or non-numeric characters from cell
For future reference, here is a more compact function to do the same
thing...
Function ExtractNum(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(S, X, 1) Like "[!0-9.]" Then Mid(S, X, 1) = " "
Next
ExtractNum = WorksheetFunction.Trim(S)
End Function
This function should be quite efficient as it does not use any
concatenations, instead using the quite efficient Mid statement to remove
the non-digits/non-dots and then using the worksheet's Trim function to
remove any leading and/or trailing spaces while collapsing any multiple
consecutive internal spaces down to single spaces.
--
Rick (MVP - Excel)
"Jacob Skaria" wrote in message
...
Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11.
From menu Insert a Module and paste the below function.Close and get back
to
workbook and try the below formula.
With your data in Cell A1 use the formula
=ExtractNum(A1)
Function ExtractNum(varData)
For intTemp = 1 To Len(varData)
If IsNumeric(Mid(varData, intTemp, 1)) Or _
Mid(varData, intTemp, 1) = Chr(32) Then
ExtractNum = ExtractNum & Mid(varData, intTemp, 1)
ElseIf Mid(varData, intTemp, 1) = "." Then
If Mid(varData, intTemp, 2) Like ".#" Then _
ExtractNum = ExtractNum & Mid(varData, intTemp, 1)
End If
Next
ExtractNum = WorksheetFunction.Trim(ExtractNum)
End Function
If this post helps click Yes
---------------
Jacob Skaria
"mmanis" wrote:
I have columns of data (one for each employee) each cell per column
represents a date and in each cell is a summary of the report each
employee
is working on. I am looking for a formula to remove all non-numerical
charactes from a given cell. For my reports, I only need to identify or
extract all the numeric characters which are the report ids. All the
numbers
end with ".##"
Cell Entry: 635.09 -LR (MG) 672.09 - LR (KM) 31.08-R
I need to extract only the number so I end up with: 635.09 672.09 31.08
with
spaces added between each number.
Can this be done?
|