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

[omairhe]

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.

 

[lrobbo314]

You can create a custom function like this.

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]

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

Dim strReplace As String: strReplace = ""

 

[NotC90]

Hi

if you are using Excel 2016 you could give this a try:

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

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

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

 

[Dryver14]

Thanks for the info

 

[Rick Rothstein]

Here is another macro that you can consider (it does not use Regular Expressions to do it "magic")...

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]

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

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
	A	B	C
1		Peter_SSs	lrobbo314
2	A8^Kite?	A8Kite	A8Kite
3	abc 450	abc450	abc450
4	4xsection-	4xsection	4xsection
5	action?	action	action
6	54	54
7	12/31/2017	12312017	12312017
8	abc	abc
9	a?bc_76	abc76	abc_76
10
11	abc%ghi()	abcghi	abcghi
Sheet1

 

[lrobbo314]

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.

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

 

[omairhe]

Thanks much for your kind solutions. I am making a record list out of huge data so I needed exactly like this.

regards,

omair