Removing non #'s from cell (letters, - , #, etc)

See Aladin's fine post -- all the way at the end, the last formula set is what you seek.

I used a UDF:<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> testVar(myStr<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Double</SPAN><SPAN style="color:darkblue">Dim</SPAN> b()<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Byte</SPAN>, i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, myNum<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>
b = StrConv(myStr, vbFromUnicode)<SPAN style="color:darkblue">For</SPAN> i =<SPAN style="color:darkblue">LBound</SPAN>(b)<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(b)
    <SPAN style="color:darkblue">Select</SPAN><SPAN style="color:darkblue">Case</SPAN> ChrW$(b(i))
        <SPAN style="color:darkblue">Case</SPAN> 0<SPAN style="color:darkblue">To</SPAN> 9, "."
            myNum = myNum & ChrW$(b(i))
    <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Select</SPAN><SPAN style="color:darkblue">Next</SPAN>
testVar = myNum ^ 1<SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>

Edit: My code formatting evaporated

=SUBSTITUTE(AB13485,RIGHT(AB13485,SUMPRODUCT((LEN(AB13485)-LEN(SUBSTITUTE(AB13485,{0,1,2,3,4,5,6,7,8,9},""))))),"")

Didn't quite work for instance:

77R5158 = 7 WANTED: 775158

A-054-4M = A-05 WANTED: 544

76DB2408 =76 WANTED: 762408

?

I have never used a user defined formula........I right clicked tab and pasted but can't seem to get it to work. Any help walking me thru my first udf would be greatly appreciatted!

MCONCAT is included in Morefunc.dll...

Hello again Craig,

In your spreadsheet hit alt-f11 to go to your VBE. Click the Insert Menu Heading -> Module. Paste the posted code in the now opened code module. You may now use this function within the workbook as I did in my example.

Thank you so much Nate! Worked great! I would love to learn more about using vba just not sure where to start?@*$#@#%$

You are welcome. There are lots of great examples on this very board eh. Also, check out the following:

http://www.mrexcel.com/board2/viewtopic.php?t=49750

http://www.mrexcel.com/articles.shtml#VBA

I suppose the following has slightly less processing involved:

<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> testVar(myStr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Double</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> b() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Byte</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, myNum <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
b = StrConv(myStr, vbFromUnicode)
<SPAN style="color:darkblue">For</SPAN> i = 0 <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(b)
    <SPAN style="color:darkblue">Select</SPAN> <SPAN style="color:darkblue">Case</SPAN> b(i)
        <SPAN style="color:darkblue">Case</SPAN> 46, 48 <SPAN style="color:darkblue">To</SPAN> 57
            myNum = myNum & ChrW$(b(i))
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Select</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
testVar = myNum
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Function</SPAN></FONT>