Find and return special Chars

[garaya5]

Hi,

I'm looking for a formula that can find and return only the special chars in a string.

Example:

String: µGUA BUFFET E LOCA€ċES LTDA
Return: µ € ċ

[Pete_UK]

In that particular case you could use this:

=SUBSTITUTE(SUBSTITUTE(A1,"GUA BUFFET E LOCA",""),"ES LTDA","")

but I suspect you are looking for something more general. You could have a formula with 26 nested SUBSTITUTE functions, each eliminating a single letter, but you could do it more efficiently with a user-defined function (although that would involve a bit of VBA).

Hope this helps.

Pete

[shg]

"Special characters" meaning everything except ... what?

[garaya5]

Everything except A-Z a-z 0-9 and space

[shg]

      -------------A------------- -B- -------C--------
  1   µGUA BUFFET E LOCA€ċES LTDA µ€ċ B1: =Special(A1)

Function Special(sInp As String) As String
    Static oRE As Object
    
    If oRE Is Nothing Then
        Set oRE = CreateObject("VBScript.RegExp")
        oRE.Global = True
        oRE.IgnoreCase = True
        oRE.Pattern = "[!A-X0-9 ]"
    End If
    Special = oRE.Replace(sInp, "")
End Function