On Mon, 24 Nov 2008 15:52:39 -0500, Ron Rosenfeld
wrote:

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))


If your strings are generally like your examples, you can return the second
number with this simpler formula:

=TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198,99))

--ron