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