How to remove numbers?

Thanks:  0
Likes:  0

# Thread: How to remove numbers?

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

2. You could use a formula like this

=MSUBSTITUTE(A1,"1234568790","")

where MSUBSTITUTE is this UDF

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

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 ]

3. On 2002-07-30 12:14, ptrader wrote:
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.

Could you give some examples of entries from which you want to remove digits?

4. On 2002-07-30 12:19, Juan Pablo G. wrote:
You could use a formula like this

=MSUBSTITUTE(A1,"1234568790","")

I was going to toss MSUBSTITUTE in. I notice you removed the author. Please edit your post and put that info in. Don't be shy...

5. Here are some sample entries.

5832 Help should read just Help

5942345 book is at home, should read just book is at home

"3 rain today"should read just "rain today"

6. On 2002-07-30 12:26, ptrader wrote:
Here are some sample entries.

5832 Help should read just Help

5942345 book is at home, should read just book is at home

"3 rain today"should read just "rain today"
MSUBSTITUTE will take them off. As a matter of fact, it will "remove" every digit from each sentence. Give it a try. Let Juan explain how to add that function to your workbook.

My Excel skill is newbie.

Using Excel 97.

Tried =MSUBSTITUTE(B1,"1234567890","")in formula bar but get error "#NAME?"

8. Copy the provided code (Select it and hit Control C)

Hit Alt F11
Select Insert | Module
hit Control V

now, go back to Excel, and try the formula again. That's not a builtin formula, but a UDF, user defined function.

9. On 2002-07-30 12:42, ptrader wrote:

My Excel skill is newbie.

Using Excel 97.

Tried =MSUBSTITUTE(B1,"1234567890","")in formula bar but get error "#NAME?"
Juan is decided to let me sweat...

( 1.) Copy Juan's MSUBSTITUTE code.
( 2.) Open your target WB (close all others).
( 3.) Activate Tools|Macro|Visual Basic Editor.
( 4.) Activate Insert|Module.
( 5.) Paste the copied code in the window entitled "...(Code)".

Now use:

=IF(LEN(B1),TRIM(MSUBSTITUTE(B1,"1234567890","")),"")

TRIM will remove any leading space after the action of MSUBSTITUTE.

10.
On 2002-07-30 12:51, Aladin Akyurek wrote:
Juan is decided to let me sweat...

Well, actually you're too slow...

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•