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?

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?

Works Perfectly - Thank you.

"Jacob Skaria" wrote:

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?

On Thu, 6 Aug 2009 09:33:07 -0700, 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?

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

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?

On Thu, 6 Aug 2009 16:07:36 -0400, "Rick Rothstein"
wrote:

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)

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

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)


"Ron Rosenfeld" wrote in message
...
On Thu, 6 Aug 2009 16:07:36 -0400, "Rick Rothstein"
wrote:

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)

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

On Thu, 6 Aug 2009 17:21:32 -0400, "Rick Rothstein"
wrote:

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

On Thu, 06 Aug 2009 19:51:51 -0400, Ron Rosenfeld
wrote:

On Thu, 6 Aug 2009 17:21:32 -0400, "Rick Rothstein"
wrote:

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

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