Prev: Ranking from one sheet to another
Next: Excel auto accumulation formulas
From: Gary''s Student on 11 May 2010 07:31
Very Nice!!
--
Gary''s Student - gsnu201002


"Rick Rothstein" wrote:

> >>How about this simple UDF:
> >>
> >>Public Function Numerals(rng As Range) As String
> >>'
> >>' gsnuxx
> >>'
> >> Dim sStr As String, i As Long, sStr1 As String
> >> Dim sChar As String
> >> sStr = rng.Value
> >> For i = 1 To Len(sStr)
> >> sChar = Mid(sStr, i, 1)
> >> If sChar Like "[0-9]" Then
> >> sStr1 = sStr1 & sChar
> >> End If
> >> Next
> >>Numerals = sStr1
> >>End Function
> >
> > That should work OK so long as there are numerals after the initial set of
> > numbers.
> >
> > For example: 123ABC6GH
> >
> > Your UDF --> 1236 and I suspect the OP would probably want 123
>
> How about this real simple UDF for that condition...
>
> Public Function Numerals(Rng As Range) As Variant
> Numerals = Val(Rng.Value)
> End Function
>
> --
> Rick (MVP - Excel)
>
> .
>
From: Jacob Skaria on 12 May 2010 00:33
You are right.
--
Jacob (MVP - Excel)


"Rick Rothstein" wrote:

> 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  | 
Pages: 1 2 3
Prev: Ranking from one sheet to another
Next: Excel auto accumulation formulas