Identifying numbers froma text

[Harish]

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

 

[Ron Rosenfeld]

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

 

[Gary''s Student]

=LEFT(A1,FIND(" - ",A1)-1)
=MID(A1,FIND(" - ",A1)+3,FIND(" of",A1)-(FIND(" - ",A1)+3))
=RIGHT(A1,LEN(A1)-(FIND(" of",A1)+3))

For the first, second, and third numbers

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

Select the column where your text strings are; click Data/Text To Columns on
Excel's menu bar. Select the Delimited option on the Step 1 dialog, then
click Next. Put a check mark in the CheckBox labeled Space on the Step 2
dialog, then click Next. On the Step 3 dialog... change the Destination cell
to the cell address you want the first split out number to go to, then click
the 2nd column in the chart and select the OptionButton labeled "Do not
import column (skip)", then do the same thing for the 4th column. Finally,
click the Finish button.

 

[Harish]

Thanks for your ideas. I was trying to get the logic behind. I work
with an automated testing software which uses Visual Basic scripting
and I was trying to incorporate your ideas by using Visual Basic
functions. Thanks to everybody for their ideas.