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 "[09]" 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:7817738C59AB4B95A75530D39F444D6D(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 