# Formula for finding Special characters

1. ## Formula for finding Special characters

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?

Thanks,
Cullen

2. ## Re: Formula for finding Special characters

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.

3. ## Re: Formula for finding Special characters

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

4. ## Re: Formula for finding Special characters

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

5. ## Re: Formula for finding Special characters

That would be wonderful!

Sorry for taking so long to reply!

Thanks,
Cullen

6. ## Re: Formula for finding Special characters

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.

7. ## Solved - Formula for finding Special characters

This is perfect shg.

Thank you very much!

thanks,
cullen

9. ## Re: Formula for finding Special characters

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?

Hi

11. ## Re: Formula for finding Special characters

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

12. ## Re: Formula for finding Special characters

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1