Formula to extract only non-special characters: A to Z, a to z and 0 to 9

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
Hello all,

Like the title here suggests, what formula will extract characters A-Z , a-z and 0-9 from a cell.

Hence omit . spaces, dots, semicolon, question-mark, exclamation marks etc.

for instance.

From
A8^Kite?
abc 450
4xsection-
action?
54
12/31/2017

To
A8Kite
abc450
4xsection
action
54
12312017



Will appreciate any help.
Thanks.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,200
Office Version
  1. 365
Platform
  1. Windows
You can create a custom function like this.

Code:
Function FilterSpecial(s As String) As String
Dim Pattern As String: Pattern = "\W"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp


With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = Pattern
End With


If regEx.Test(s) Then
    FilterSpecial = regEx.Replace(s, strReplace)
End If


End Function

You will need to go into the VB Editor and go to Tools-->References, and add a reference to Microsoft VBScript Regular Expressions.
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
never seen this before is it just a case of not using another line to say strReplace = ""

Dim strReplace As String: strReplace = ""
 
Last edited:

NotC90

New Member
Joined
Jun 24, 2016
Messages
26
Hi

if you are using Excel 2016 you could give this a try:
Code:
=TEXTJOIN("",1,REPT(MID(A1,ROW(INDIRECT("1:999")),1),IFERROR(--SEARCH(MID(A1,ROW(INDIRECT("1:999")),1),"0|1|2|3|4|5|6|7|8|9|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z")>0,0)))

Haven't tested it though, since i dont have Excel 2016 on this PC...
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,200
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

never seen this before is it just a case of not using another line to say strReplace = ""

Dim strReplace As String: strReplace = ""

Yeah, exactly. And the code could be written like this too I suppose..

Code:
Function FilterSpecial(s As String) As String
Dim Pattern As String: Pattern = "\W"
Dim regEx As New RegExp


With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = Pattern
End With


If regEx.test(s) Then
    FilterSpecial = regEx.Replace(s, "")
End If


End Function
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,167
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Here is another macro that you can consider (it does not use Regular Expressions to do it "magic")...
Code:
Function AlphaNumeric(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!A-Za-z0-9]" Then Mid(S, X) = " "
  Next
  AlphaNumeric = Replace(S, " ", "")
End Function
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
@ lrobbo314
Your function doesn't seem to quite do what the OP has asked. For example,
- rows 6 & 8 below where the original text doesn't contain any characters that require removal (caused by your test in If regEx.Test(S) Then returning False)
- per row 9 below, your pattern allows through an underscore (because \W does not include the underscore character)

@ omairhe
Here is another regular expression function that I believe does what you want. My function also does not require you to do anything with Tools -> References in the vba window.

Code:
Function NonSpec(S As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "[^a-zA-Z0-9]"
  NonSpec = RX.Replace(S, "")
End Function

Excel Workbook
ABC
1Peter_SSslrobbo314
2A8^Kite?A8KiteA8Kite
3abc 450abc450abc450
44xsection-4xsection4xsection
5action?actionaction
65454
712/31/20171231201712312017
8abcabc
9a?bc_76abc76abc_76
10
11abc%ghi()abcghiabcghi
Sheet1
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,200
Office Version
  1. 365
Platform
  1. Windows
Right you are Peter. But the \W parameter does work as well. Minus the underscores. But it does fix the issue with the numbers. I just needed to take out the regEx.Test portion of the code.

Code:
Function FilterSpecial(s As String) As String
Dim Pattern As String: Pattern = "\W"
Dim regEx As New RegExp


With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = Pattern
End With


FilterSpecial = regEx.Replace(s, "")


End Function
 
Last edited:

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
Thanks much for your kind solutions. I am making a record list out of huge data so I needed exactly like this.

regards,

omair
 

Forum statistics

Threads
1,136,800
Messages
5,677,809
Members
419,721
Latest member
StuckInWork

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
Top