remove alpha or non-numeric characters from cell

[mmanis]

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?

 

[Jacob Skaria]

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

 

[mmanis]

Works Perfectly - Thank you.

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

 

[Ron Rosenfeld]

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?

Can be done easily with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=RemNonNum(A1)

in some cell.

==============================
Option Explicit
Function RemNonNum(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([^\d.]+)"
RemNonNum = re.Replace(s, " ")
End Function
==============================

Note that the above removes all characters that are NOT digits or a ".".
Perhaps to be a bit more robust, and ensure that everything except a digit
string ending in .## is removed, you might try this UDF instead:

=================================
Option Explicit
Function RemNonNum(s As String) As String
Dim sRes() As String
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\b\d+\.\d\d\b"
If re.test(s) = True Then
Set mc = re.Execute(s)
ReDim sRes(0 To mc.Count - 1)
For Each m In mc
sRes(i) = m
i = i + 1
Next m
End If
RemNonNum = Join(sRes)
End Function
===================================
--ron

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

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,

Your function does not work the same as Jacob's.

In particular, if the source string has dots that are not part of a number,
your function will return them, whereas Jacob's will not.

Example string:

635.0 -LR... (MG) 672.09 - LR (KM) 31.08-R

Jacob's: 635.0 672.09 31.08
Rick's: 635.0 ... 672.09 31.08


In addition, the OP mentioned that all of the desired numbers end with ".##" Of
the posted solutions, only the second regex UDF in my post will differentiate
numbers in that format from numbers not ending with ".##"

--ron

 

[Rick Rothstein]

Good point! Looks like it's back to the drawing boards.<g>

By the way, maybe a slight problem with your 2nd UDF... while we don't know
all the possible constructions for the OP's strings, your code will approve
numbers ending in ".##" if it is adjacent to a non-alpha characters and
disapprove those number if it is adjacent to an alpha character. That is,
something like "?1.23+" will be approved where as "x1.23z" will not.

--
Rick (MVP - Excel)

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,

Your function does not work the same as Jacob's.

In particular, if the source string has dots that are not part of a
number,
your function will return them, whereas Jacob's will not.

Example string:

635.0 -LR... (MG) 672.09 - LR (KM) 31.08-R

Jacob's: 635.0 672.09 31.08
Rick's: 635.0 ... 672.09 31.08


In addition, the OP mentioned that all of the desired numbers end with
".##" Of
the posted solutions, only the second regex UDF in my post will
differentiate
numbers in that format from numbers not ending with ".##"

--ron

 

[Ron Rosenfeld]

Good point! Looks like it's back to the drawing boards.<g>

By the way, maybe a slight problem with your 2nd UDF... while we don't know
all the possible constructions for the OP's strings, your code will approve
numbers ending in ".##" if it is adjacent to a non-alpha characters and
disapprove those number if it is adjacent to an alpha character. That is,
something like "?1.23+" will be approved where as "x1.23z" will not.

Well, we know from his example that 31.08- should be accepted.

If the assumption is that any construct of a number ending in ".##" should be
accepted, so long as it is not embedded within a longer number, then the
following should accomplish that:

==========================================
Option Explicit
Function RemNonNum(s As String) As String
Dim sRes() As String
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(^|\D)(\d+\.\d\d)(\D|$)"
If re.test(s) = True Then
Set mc = re.Execute(s)
ReDim sRes(0 To mc.Count - 1)
For Each m In mc
sRes(i) = m.submatches(1)
i = i + 1
Next m
End If
RemNonNum = Join(sRes)
End Function
==========================================
--ron

 

[Ron Rosenfeld]

Well, we know from his example that 31.08- should be accepted.

If the assumption is that any construct of a number ending in ".##" should be
accepted, so long as it is not embedded within a longer number, then the
following should accomplish that:

==========================================
Option Explicit
Function RemNonNum(s As String) As String
Dim sRes() As String
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(^|\D)(\d+\.\d\d)(\D|$)"
If re.test(s) = True Then
Set mc = re.Execute(s)
ReDim sRes(0 To mc.Count - 1)
For Each m In mc
sRes(i) = m.submatches(1)
i = i + 1
Next m
End If
RemNonNum = Join(sRes)
End Function
==========================================
--ron

And here is a shorter regex routine that uses the Replace function, so likely
will work more quickly (using the same assumptions as above):

=======================================
Option Explicit
Function RemNonNum(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = ".*?(^|\D)(\d+\.\d\d)(\D|$)|.*"
RemNonNum = Trim(re.Replace(s, "$2 "))
End Function
============================
--ron