Extracting Only Numeric Characters from Cells

[wren17]

Hello Everyone:

I am looking for a formula or Macro that will extract Only the numeric characters from a cell.
Example: I have a cell with "TM310".
I want a formula that will extract only the 310 and exclude the TM.

Now I am well aware of left,right, and mid formulas, but what makes this difficult is that I have thousands of cells and the models change. sometimes it looks like "TM310V" or "S123-VC", so it makes hard coding a set value like right(A1,3) not at all usefull.

Any assistance would be greatly apprecatiated

thanks,
Wren

 

[Joe4]

Check out the user defined function I wrote in the following thread:

http://www.mrexcel.com/board2/viewtopic.php?t=54495&highlight=numeric+user+function

 

[NateO]

Here's another function. The only reason I add it is due to performance implications, although both are quick:<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> CombNums(myStr<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN><SPAN style="color:darkblue">Dim</SPAN> b()<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Byte</SPAN>, z<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>
b = myStr<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">Step</SPAN> 2
    <SPAN style="color:darkblue">If</SPAN> ChrW$(b(i))<SPAN style="color:darkblue">Like</SPAN> "#"<SPAN style="color:darkblue">Then</SPAN> z = z & ChrW$(b(i))<SPAN style="color:darkblue">Next</SPAN>
Erase b
CombNums = z
z =<SPAN style="color:darkblue">Empty</SPAN><SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>

Here's some examples of it's use:

Book1
	A	B	C	D
1	sdfh34-4sjdfk3	TM310VorS123-VC	TM310V	S123-VC
2	3443	310123	310	123
3	234
Sheet3

If you have a lot of these, you'll want to reduce your lead-time as muchh as possible. This underwent limited testing. Have a nice day.

 

[Aladin Akyurek]

Try...

=--MCONCAT(IF(ISNUMBER(SETV(MID(A1,INTVECTOR(LEN(A1),1,,1),1))+0),GETV(),""))

which needs to be array-entered, that is, using control+shift+enter.

This formula requires the morefunc add-in.

Book5
	A	B	C	D
1	TM310	310
2	TM310V	310
3	S123-VC	123
4
5
Sheet1

If you prefer, you can use the following whic does not require morefunc...

=--ACONCAT(IF(ISNUMBER(V(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+0),V(),""))

Also array-entered. ACONCAT and V are UDFs in VBA, whose codes are available on this site.

 

[NateO]

Aladin, I can't find udf = v via the search. I found aconcat...

In any case, I would like to replicate the aconcat solution, would you mind providing the code for v or a pointer?

Thanks in advance,
Nathan

 

[Aladin Akyurek]

Aladin, I can't find udf = v via the search. I found aconcat...

In any case, I would like to replicate the aconcat solution, would you mind providing the code for v or a pointer?

Thanks in advance,
Nathan

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function