# Extract a number from a text string?

Brian
Guest
Posts: n/a

 9th Jan 2006
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
Guest
Posts: n/a

 9th Jan 2006
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.

"Brian" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

=?Utf-8?B?Q0xS?=
Guest
Posts: n/a

 9th Jan 2006
=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

"Brian" wrote:

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

Niek Otten
Guest
Posts: n/a

 9th Jan 2006
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
'
=============================================================================

--
Kind regards,

Niek Otten

"Bob Umlas" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>

>
>

RagDyeR
Guest
Posts: n/a

 9th Jan 2006
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!
=====================================================

"Brian" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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
Guest
Posts: n/a

 9th Jan 2006
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)

"Brian" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

Ron Rosenfeld
Guest
Posts: n/a

 9th Jan 2006
On Mon, 9 Jan 2006 09:26:14 -0600, "Brian" <(E-Mail Removed)> wrote:

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

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
Guest
Posts: n/a

 9th Jan 2006
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
Guest
Posts: n/a

 9th Jan 2006
On 9 Jan 2006 12:27:44 -0800, "Harlan Grove" <(E-Mail Removed)> wrote:

>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+".

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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Access Joe Microsoft Excel Worksheet Functions 6 1st Dec 2009 08:35 PM MeatLightning Microsoft Excel Misc 16 19th Nov 2008 02:08 AM =?Utf-8?B?bmFzdGVjaA==?= Microsoft Excel Misc 5 5th Jul 2006 11:21 PM Karl Burrows Microsoft Excel Discussion 23 25th Jun 2005 10:37 PM =?Utf-8?B?UmhvXzFy?= Windows XP General 0 16th Sep 2004 05:49 AM

Features