You could use a formula like this
=MSUBSTITUTE(A1,"1234568790","")
where MSUBSTITUTE is this UDF<pre>Function MSUBSTITUTE(ByVal trStr As Variant, frStr As String, toStr As String) As Variant
' Created by Juan Pablo González
' with ideas from Aladin Akyurek
'toStr is assumed to be the same length of frStr. If not, the remaining characters
'will be considered as null ("").
'Note that this function IS case sensitive. To replace all instances of "a" you need
'to use "a" AND "A"
'You can't replace one character with two characters. This
'=MSUBSTITUTE("This is a test","i","$@")
'will result in this:
'"Th$s $s a test"
Dim iRow As Integer
Dim iCol As Integer
Dim j As Integer
Dim Ar As Variant
Dim vfr() As String
Dim vto() As String
ReDim vfr(1 To Len(frStr))
ReDim vto(1 To Len(frStr))
For j = 1 To Len(frStr)
vfr(j) = Mid(frStr, j, 1)
If Mid(toStr, j, 1)<> "" Then
vto(j) = Mid(toStr, j, 1)
Else
vto(j) = ""
End If
Next j
If IsArray(trStr) Then
Ar = trStr
For iRow = LBound(Ar, 1) To UBound(Ar, 1)
For iCol = LBound(Ar, 2) To UBound(Ar, 2)
For j = 1 To Len(frStr)
Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j))
Next j
Next iCol
Next iRow
Else
Ar = trStr
For j = 1 To Len(frStr)
Ar = Application.Substitute(Ar, vfr(j), vto(j))
Next j
End If
MSUBSTITUTE = Ar
End Function</pre>
Edit: Ok Aladin, there it is...
_________________
Regards,
Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-07-30 12:30 
