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:
So if i had an address in cell A2 that was "123° Fake Street" the result in cell B2 would be CHAR(176)
Please Login or Register to view this content.
Is something like this possible?
Any ideas would be helpfull!
Thanks,
Cullen
Last edited by Cullen8; 05-19-2009 at 06:25 PM. Reason: Solved
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.
Entia non sunt multiplicanda sine necessitate
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
I can give you a function like CLEAN that will get rid of all of them, if you prefer.
That would be wonderful!
Sorry for taking so long to reply!
Thanks,
Cullen
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.
This is perfect shg.
Thank you very much!
thanks,
cullen
You're welcome. Would you please mark the thread as solved?
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
please take a moment to read the forum rules and then start your own thread.
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
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks