delete non-numeric characters in a string

[novice_2010]

Hello, All

Suppose cell A1 is "~1&2_a3^4d", I want to delete those non-numeric characters so that cell A1 becomes "1234". Is thee a way to do it? Thanks.

 

[Dryver14]

Good morning Novice (I'm a novice too)

There are several methods for reaching just numeric charachters in a cell
find, replace, substitute etc most numbers do not come as complicated as the one you have posted and I would say if you had only a few like that then just type out the numbers as you have.

 

[hiker95]

novice_2010,

Excel Workbook
	A	B
1	~1&2_a3^4d	1234
2
Sheet1

The array formula in cell B1 confirmed with CTRL + SHIFT + ENTER (not just ENTER):

=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))* ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

 

[novice_2010]

Thank you very much, hiker95. That is really a very complicated formula. I may need some time to digest :--)

Dryver14, thanks for your suggestion. I can do it manually; but when data set is big, manually editing every single cell is really boring and tiring :--)

 

[Dryver14]

Novice,

I only suggested doing it maunally if there were a few the point I was trying to make is that Hikers solution is brilliant but if your set of values just contained something like 6 * - you could just use find and replace.

 

[Armando Montes]

You might try this UDF:

'http://www.mrexcel.com/forum/showthread.php?t=362461
Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(ref = True, "\d+", "\D+")
    .Global = True
    TextNum = .Replace(txt, "")
End With
End Function
'=TextNum(A1,1)
'1 for Text only, 0 for Numbers only