How to remove numbers?

ptrader

New Member
Joined
Jul 29, 2002
Messages
24
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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... :biggrin:

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-07-30 12:30
 
Upvote 0
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.

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

Thanks in advance.

Ptrader

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

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

:biggrin:
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
Thanks Juan and Aladin.

My Excel skill is newbie.

Using Excel 97.

Tried =MSUBSTITUTE(B1,"1234567890","")in formula bar but get error "#NAME?"
 
Upvote 0
Copy the provided code (Select it and hit Control C)

Go to your workbook
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.
 
Upvote 0
On 2002-07-30 12:42, ptrader wrote:
Thanks Juan and Aladin.

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... :biggrin:

( 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)".
( 6.) Activate File|Close and Return to Microsoft Excel.

Now use:

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

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

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top