Closed Thread
Results 1 to 12 of 12

Formula for finding Special characters

  1. #1
    Registered User
    Join Date
    04-01-2008
    Location
    Houston
    Posts
    68

    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?

    Any ideas would be helpfull!

    Thanks,
    Cullen
    Last edited by Cullen8; 05-19-2009 at 06:25 PM. Reason: Solved

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,138

    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.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-01-2008
    Location
    Houston
    Posts
    68

    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. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,138

    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. #5
    Registered User
    Join Date
    04-01-2008
    Location
    Houston
    Posts
    68

    Re: Formula for finding Special characters

    That would be wonderful!

    Sorry for taking so long to reply!

    Thanks,
    Cullen

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,138

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

  7. #7
    Registered User
    Join Date
    04-01-2008
    Location
    Houston
    Posts
    68

    Solved - Formula for finding Special characters

    This is perfect shg.

    Thank you very much!

    thanks,
    cullen

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,138

    Re: Formula for finding Special characters

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

  9. #9
    Registered User
    Join Date
    11-17-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    1

    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?

  10. #10
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: Formula for finding Special characters

    Hi

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

  11. #11
    Registered User
    Join Date
    03-18-2010
    Location
    Mexico
    MS-Off Ver
    Excel 2010
    Posts
    1

    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. #12
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    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

Closed Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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