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