From: Ron Rosenfeld on 10 May 2010 23:06 On Mon, 10 May 2010 22:54:11 -0400, "Rick Rothstein" 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" 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" 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" 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" 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 anotherNext: Excel auto accumulation formulas