Prev: Ranking from one sheet to another
Next: Excel auto accumulation formulas
From: Ron Rosenfeld on 10 May 2010 23:06
On Mon, 10 May 2010 22:54:11 -0400, "Rick Rothstein"
<rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote:

>How about this real simple UDF for that condition...
>
>Public Function Numerals(Rng As Range) As Variant
> Numerals = Val(Rng.Value)
>End Function

Now the OP has both a VBA and a worksheet function solution.
--ron
From: ozgrid.com on 11 May 2010 00:01
See http://www.ozgrid.com/VBA/ExtractNum.htm

which can handle Decimal and negative values, or not.



--
Regards
Dave Hawley
www.ozgrid.com
"Bam" <Bam(a)discussions.microsoft.com> wrote in message
news:9F4F692B-5B46-4DDB-9699-415114984448(a)microsoft.com...
> Hi All,
>
> I have a colmun of Codes, of varying lengths that contains Letters at the
> end of each Code.
>
> The letters range from 1 single letter up to a word, again of varying
> lengths.
>
> I need to strip out all alpha letters and retain only the numeric numbers.
>
> Either a fomula or Macro.
>
> Many Thanks,
>
> Bam.
>
>

From: Rick Rothstein on 11 May 2010 00:59
> See http://www.ozgrid.com/VBA/ExtractNum.htm
>
> which can handle Decimal and negative values, or not.

Given the OP wants to grab the number which is located at the beginning of
the text, here is a shorter UDF that has the same functionality as your UDF
does for this situation...

Public Function Numerals(Rng As Range, Optional Take_decimal As Boolean, _
Optional Take_negative As Boolean) As Double
Numerals = Val(Rng.Value)
If Not Take_decimal Then Numerals = Replace(Numerals, ".", "")
If Not Take_negative Then Numerals = Replace(Numerals, "-", "")
End Function

--
Rick (MVP - Excel)



"ozgrid.com" <dave(a)ozgrid.com> wrote in message
news:#9W3o6L8KHA.1316(a)TK2MSFTNGP02.phx.gbl...
> See http://www.ozgrid.com/VBA/ExtractNum.htm
>
> which can handle Decimal and negative values, or not.
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Bam" <Bam(a)discussions.microsoft.com> wrote in message
> news:9F4F692B-5B46-4DDB-9699-415114984448(a)microsoft.com...
>> Hi All,
>>
>> I have a colmun of Codes, of varying lengths that contains Letters at the
>> end of each Code.
>>
>> The letters range from 1 single letter up to a word, again of varying
>> lengths.
>>
>> I need to strip out all alpha letters and retain only the numeric
>> numbers.
>>
>> Either a fomula or Macro.
>>
>> Many Thanks,
>>
>> Bam.
>>
>>
>
From: Jacob Skaria on 11 May 2010 02:54
=LOOKUP(99^99,--("0"&MID(A1,1,ROW($1:$10000))))

--
Jacob (MVP - Excel)


"Bam" wrote:

> Hi All,
>
> I have a colmun of Codes, of varying lengths that contains Letters at the
> end of each Code.
>
> The letters range from 1 single letter up to a word, again of varying lengths.
>
> I need to strip out all alpha letters and retain only the numeric numbers.
>
> Either a fomula or Macro.
>
> Many Thanks,
>
> Bam.
>
>
From: Rick Rothstein on 11 May 2010 04:09
I don't think you need to concatenate the "0" onto the front of the MID
function which means you can also remove a couple of parentheses. I believe
this will work...

=LOOKUP(99^99,--MID(A1,1,ROW($1:$10000)))

You can save two additional characters by using the LEFT function instead of
the MID function...

=LOOKUP(99^99,--LEFT(A1,ROW($1:$1000)))

--
Rick (MVP - Excel)



"Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message
news:7817738C-59AB-4B95-A755-30D39F444D6D(a)microsoft.com...
> =LOOKUP(99^99,--("0"&MID(A1,1,ROW($1:$10000))))
>
> --
> Jacob (MVP - Excel)
>
>
> "Bam" wrote:
>
>> Hi All,
>>
>> I have a colmun of Codes, of varying lengths that contains Letters at the
>> end of each Code.
>>
>> The letters range from 1 single letter up to a word, again of varying
>> lengths.
>>
>> I need to strip out all alpha letters and retain only the numeric
>> numbers.
>>
>> Either a fomula or Macro.
>>
>> Many Thanks,
>>
>> Bam.
>>
>>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Ranking from one sheet to another
Next: Excel auto accumulation formulas