How to remove numbers?

[ptrader]

I'm trying to remove all numbers from text.

I'm using
=SUBSTITUTE(SUBSTITUTE(A1," ",""),"18945","")

Number "18945" is just a sample. It could be any number and 1 to 20 digits. It varies with each entry.

Also how could ai add some default text to each entry.

If I already have result of "Please Help" how could I add "ASAP" to each cell result?

Thanks in advance.

Ptrader

 

[ptrader]

Tried the code but get,

"Compile error" "Invalid outside procedure"

Stops on the "1" at this line ReDim vfr(1 To Len(frStr))

Thanks

 

[Juan Pablo González]

Weird... maybe it's a 97 thing. Try using this instead

a = Len(frStr)
ReDim vfr(1 To a)

 

[ptrader]

If I enter it this way

a = Len(frStr)
ReDim vfr(1 To a)
It stops at "frStr"
and "ivalid outside procedure"

If I enter it this way
ReDim vfr(1 To a) a = Len(frStr)

It stops at second "a" and "Expected:end of statement


I'm I not entering correctly?

Ptrader

 

[Juan Pablo González]

Are you sure you copied right ?

<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>

 

[ptrader]

Thanks for all your help.

It works great now that I recopied it.

Must of missed something.

Ptrader

 

[olives]

You could use a formula like this...

Increible. Que bonita funcion! Gracias!

This is exactly what I was looking for! Who came up with this function? I love it