Put this code in a REGULAR module and then

=striptxt(a1)

Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If Asc(b) 47 And Asc(b) < 58 Then StripTxt = StripTxt + b
Next i
End Function



--
Don Guillett
SalesAid Software

"Monk" wrote in message
...
Hello..

I did post this question yesterday and following a fantastic response I
thought I had it rapid up..
However the cool formula provided has a slight defect to it..

Ok.. The problem..., I have a vast list of cells with numbers with in them
(which is good) however the string of numbers sometimes commence with the
'character and sometimes may end with a 'character or both in some cases,
(which is bad).

Example A1: '1234435999 A2: 34566673341 A3: '444532322711'

It appears completely random whether the cells contain any 'character or
is
completely free from the little blighters.
So I need a formula (rather then macro) to remove all the '''' characters
in
one go (rather then using the longwinded Left / Right formula)

Yesterday I was given the following impressive formula.
B1
=LEFT(MID(P29,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},P29 &"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}," 0123456789"&P29))),SUMPRODUCT(--ISNUMBER(--MID(P29,ROW(INDIRECT("1:"&(LEN(P29)))),1))))

This appeared to work perfectly, however I have since noticed if the cell
ENDS with any consecutive digits (ie. 35999) the formula only appears to
generate one of the digits (359). Can anyone amend the formula or provide
a
new one to get around my dilemma ..?

Many thanks,
Monk