Return only numbers in a cell

[marshy3300]

Hi Pros and Hacks alike,

I have cells that contain both #s and characters but only ant the numbers (sort of)
EG.

E3= 1415 x 1455
E4= 1400* x 1460
E5= 1460mm x 1460mm
E6= 1455w x 1780L

Basically i want it only to show as "####" x "####"

Any help would be awesome

 

[Michael M]

How about a UDF
Copy this into a Standard Module

Function num(ByVal txt As String) As String
Dim X As Long     'Code base by Rick Rothstein (MVP - Excel)
    For X = 1 To Len(txt)
        If Mid(txt, X, 1) Like "*[!0-9 x ]*" Then Mid(txt, X, 1) = Chr(1)
    Next
num = Replace(txt, Chr(1), "")
End Function

Then use the formula as follows

Excel 2010
	E	F
2	1415 x 1455	1415 x 1455
3	1400* x 1460	1400 x 1460
4	1460mm x 1460mm	1460 x 1460
5	1455w x 1780L	1455 x 1780
Sheet3

Cell Formulas
Range	Formula
F2	=num(E2)
F3	=num(E3)
F4	=num(E4)
F5	=num(E5)

 

[marshy3300]

Thanks Michael, No idea at ALL what you just said, but you are brilliant!!

 

[Michael M]

Glad it worked, and thanks for the feedback......you'll note in the code though that one of our other posters Rick Rothstein is the brilliant one !!...