# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Formula for finding Special characters

## Cullen8

Hi All,

I was hoping someone could help me out.  I've been trying to put together a formula to find special characters with no luck.  

Characters Im looking for are:




```
Please Login or Register  to view this content.
```


So if i had an address in cell A2 that was "123° Fake Street"  the result in cell B2 would be CHAR(176)

Is something like this possible?

Any ideas would be helpfull!  

Thanks,
Cullen

----------


## shg

If you want to find them to get rid of them, you can use CLEAN to get rid of everything below 32.

For most characters, you can use the Find dialog by using NUMERIC keypad to enter the character code with the Alt key pressed. For example, to find the degree symbol, enter Alt+0176 in the Find what box.

----------


## Cullen8

Thanks shg,

The file i'm getting is from our HR system.  The data needs to be cleaned there.  I'm just trying to find the records that need to be scrubbed...

Your idea might work i think though...  I can do something like =if(A2<>clean(A2),"Possible Special Characters","")

I'll test and see if something like this will work!

Thanks for the help!
Cullen

----------


## shg

I can give you a function like CLEAN that will get rid of all of them, if you prefer.

----------


## Cullen8

That would be wonderful!  :Smilie: 

Sorry for taking so long to reply!

Thanks,
Cullen

----------


## shg

I did this for my own use, so it's not terribly user friendly. Someday I'll put in a userform and make it an add-in. In the meanwhile, ...

Open the attached workbook. The sheet contains a Char column and Map column. 

If you want the character unchanged, just drag the formula from the Char column across (most are already that way). 

If you want to delete the character, clear the contents of the Map column. 

If you want some other character substituted, put whatever character you want in there. 

To use it, open the other workbook, and (with both workbooks open), use

=CharMap.xls!sMap(A1)

There a static array that contains the mapping. If you change the mapping in the Map column, run macro MapReset to make it reinitialize.

----------


## Cullen8

This is perfect shg.

Thank you very much!

thanks,
cullen

----------


## shg

You're welcome. Would you please mark the thread as solved?

----------


## h_tere2000

I tried to use your MAP, but i find it is no different from CLEAN function.

It works just like clean.

But strangely i encountered some kind of data which is a blank spacing.

eg.

APPLE SDN BHD |<--  Mysterious spacing
APPLE SDN BHD|<--

I wrote a vba script to show the chr(data_) but the result is
A|
P|
P|
L|
E|
|32
S|
D|
N|
|32
B|
H|
D|78

it seems normal, but in excel it is a blank space that cannot be TRIM() or CLEAN(), after paste special as values.

Have you encountered anything so strange?

----------


## teylyn

Hi

please take a moment to read the forum rules and then start your own thread.

----------


## joseeusebio

Try this code, add to some VBA module and call it as a typical formula, I mean, let's supose a value to verify is "123x@" and is located in A1, use the function as a 
formula, for example write =IsThereSpecialChars(A1) in B1 cell and the value returned will be TRUE 

Public Function IsThereSpecialChars(ByVal Src As Range) As Boolean
'Created by José Eusebio López
  Dim L As Long, I As Long, Char As String * 1
  L = Len(Src)
  For I = 1 To L
    Char = Mid$(Src.Value, I, 1)
    Select Case Char
      Case "0" To "9", "A" To "Z"
        IsThereSpecialChars = False
      Case Else
        IsThereSpecialChars = True
        Exit For
    End Select
  Next
End Function

----------


## teylyn

joseeusebio,

please take a long, intense look at the forum rules. This thread is almost a year old. It does not require any more replies. Also, use code tags when posting code.

thanks

----------

