On Mon, 24 Nov 2008 12:15:27 -0800 (PST), Harish
wrote:

Hi,

I have a small problem. I have a text string: "1 - 7 of 61". I want to
pick out the numbers in this text string. Also note that this text
string is not constant and it keeps changing. For example, we have:
"21 - 40 of 100". So I want to pick the numbers individually from each
text string. Do anyone have a formula for that? Appreciate your help.
Thanks

Will the numbers always be the first, third and last sequences, as in your
examples?

If so, then

First number: =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)

Second number:
=LEFT(TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1),
FIND(" ",TRIM(A1))+1),255)),FIND(" ",TRIM(MID(TRIM(A1),
FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),255)))-1)

Third number: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

Or, if the numbers will always consist of series of digits, and will be
unsigned, you could write a short UDF.

If the numbers might be signed, or might include a decimal, then the UDF would
need a minor change.

To do this <alt-F11 opens the VB Editor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens.

You can then use this formula:

=GetNums(A1,Index)

Where A1 contains the string, and Index is the position - 1 is the first set of
digit(s), 2 is the second, and so forth.

=================================
Option Explicit
Function GetNums(str As String, Index As Long)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
Set mc = re.Execute(str)
If mc.Count < Index Then
GetNums = CVErr(xlErrNum)
Else
GetNums = CDbl(mc(Index - 1))
End If
End Function
===============================
--ron