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

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

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

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

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

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

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
>

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

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

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