Extract a number from a text string?

[Brian]

Howdy All,

Just curious if this is possible.

I have a text string in a cell A1 such as "vol 25k/mo".

I want to be able to extract just the '25' from that string for use in a
formula.

Is there a way to do this?

Thanks,
Brian

 

[Bob Umlas]

Well, heres ONE way - requires 2 steps.
If A1 has the string, Ctrl/shift/enter this in B1
=MATCH(TRUE,ISERROR(1*MID(MID(A1,MATCH(FALSE,ISERROR(1*MID($A1,COLUMN(A:Z),1
)),0),40),COLUMN(A:Z),1)),0)-1
and ctrl/shift/enter this in C1 (the answer):
=1*LEFT(MID(A1,MATCH(FALSE,ISERROR(1*MID($A1,COLUMN(A:Z),1)),0),40),B1)
I imagine there's an easier way, but it's the first way that comes to mind.
Probably easiest would be a UDF.

 

[Guest]

=MID(A1,FIND(" ",A1,1),3)*1
will extract the 25 as a number for other calculations in your sample data.

The details are specific to the exact configuration of the data to be
interrogated.

Vaya con Dios,
Chuck, CABGx3

 

[Niek Otten]

Here is a UDF. If you're new to VBA, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The function returns text, so use it like:

=VALUE(StripTxt(A1))

'
=============================================================================
Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If (Asc(b) > 47 And Asc(b) < 58) Or b = Application.DecimalSeparator _
Then StripTxt = StripTxt + b
Next i
End Function
'
=============================================================================

 

[RagDyeR]

This *only* works if you have *one string* of numbers, with *no spaces*
between the numbers,
Spaces anywhere else are acceptable:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

This is an *array* formula, so ...
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Howdy All,

Just curious if this is possible.

I have a text string in a cell A1 such as "vol 25k/mo".

I want to be able to extract just the '25' from that string for use in a
formula.

Is there a way to do this?

Thanks,
Brian

 

[Bob Phillips]

This non-array formula will get a contiguous numeric string

=LOOKUP(9.99999999999999E+307,--MID(A21,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A21
&"0123456789")),ROW(INDIRECT("1:"&LEN(A21)))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Howdy All,

Just curious if this is possible.

I have a text string in a cell A1 such as "vol 25k/mo".

I want to be able to extract just the '25' from that string for use in a
formula.

Is there a way to do this?

Thanks,
Brian

In addition to the above, you can download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr and use the "regular
expression" formula:

=--REGEX.MID(A5,"\d+")

(The double unary at the beginning transforms what would be a string to a
number).

If the number in the string might have a decimal point, then:

=--REGEX.MID(A5,"\d+(\.\d+)?")


--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

If the number in the string might have a decimal point, then:

=--REGEX.MID(A5,"\d+(\.\d+)?")

Begging the question what the numeric substring should be in "Rifle,
..22 caliber". The point is that periods following decimal numerals but
not preceding more decimal numerals can be ignored, but periods not
following decimal numerals but preceding decimal numerals shouldn't be
ignored. Use "(\d*\.)?\d+".

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

Begging the question what the numeric substring should be in "Rifle,
.22 caliber". The point is that periods following decimal numerals but
not preceding more decimal numerals can be ignored, but periods not
following decimal numerals but preceding decimal numerals shouldn't be
ignored. Use "(\d*\.)?\d+".

Thank you for that correction.

And I would expect to return .22 from the above string and not 22 -- which your
expression does correctly and mine does not.


--ron