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" 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 anotherNext: Excel auto accumulation formulas