Substitute all letters of the alphabet

[Rubber Beaked Woodpecker]

Hi

I would like a formula that substitute all letters from the alphabet. I know I can do this using a rule for each letter but obviously that would create a very long formula.

Is there an easier way please?

 

[RoryA]

When you say substitute, do you mean remove, or replace with something else? Either way, if you are still using 2016, I think it will be a long formula unless you use a UDF written in VBA.

 

[Rubber Beaked Woodpecker]

When you say substitute, do you mean remove, or replace with something else? Either way, if you are still using 2016, I think it will be a long formula unless you use a UDF written in VBA.

Hi

Remove

 

[RoryA]

Then I think your best bet is VBA unless you have access to a more recent version of Office.

 

[Rubber Beaked Woodpecker]

OK Thank you

 

[Peter_SSs]

So, are you open to a vba approach? If so, you could consider a user-defined function. Here is one. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I took a punt about what you might want to do in relation to any space characters in the data, if they are possible. If it is not what you want, please detail what you do want.

Function RemoveLetters(s As String) As String
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Za-z]"
    RemoveLetters = Application.Trim(.Replace(s, ""))
  End With
End Function

RBW.xlsm
	A	B
1	Sell 10 toys	10
2	What the heck!!??@@##!!	!!??@@##!!
3
4	ABCDefgh
5	23 abc 46 def 123	23 46 123
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=RemoveLetters(A1)

 

[RoryA]

For example:

Function ReplaceAlpha(inputText As String) As String
   If Len(inputText) <> 0 Then
      Dim n As Long
      For n = 1 To 26
         inputText = Replace$(inputText, Chr(64 + n), "", , , vbTextCompare)
      Next n
      ReplaceAlpha = inputText
   End If
End Function