+ Reply to Thread
Results 1 to 11 of 11

Remove Special Characters

  1. #1
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Remove Special Characters

    Greetings,
    A user had exported a .csv from our online db and when she opened the document in Excel 2007 it had the "question-mark-in-the-box" special character between each word (the place where a space would be) in most, but not all rows.

    My question is how to remove this special character from the entire document.

    Thanks so much,
    Briz
    Last edited by bkatzman; 04-13-2010 at 11:51 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove Special Characters

    First find out what Character code it is...

    example.

    =Code(Mid(a1,4,1)) where A1 contains a sample string with that character at the 4th position.

    After you find out the code....

    Then go to Edit|Replace

    In the Find what box: Hold the ALT key down and enter a 0 (zero) and the code only using the numeric key pad.

    In the Replace With box: enter a space with the spacebar

    Click Replace All.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Remove Special Characters

    Greetings,
    The =Code function revealed the special character to be 117.
    I opened, Find and Replace, alt+0117 (only using keypad) which resulted in "u" in the Find line. I hit a space in the Replace line, but Excel didn't find any "u"...

    Any ideas? Thanks for your help as always!
    Briz

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove Special Characters

    Are you sure you got the right special char.. that is a regular lowercase "u".

    Is there anyway to attach a small piece of the spreadsheet (by cutting out most of it).

  5. #5
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Remove Special Characters

    Sorry about that I realized that the special character is at the eleventh position, which then reveals special character code 9. When I use ALT+09 nothing gets entered into the Find line, when I use ALT+9 another character comes up (that is not in the row).

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove Special Characters

    Try 009 in the Find What box.

  7. #7
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Remove Special Characters

    No dice. I attached the document if it helps...
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove Special Characters

    maybe that doesn't work with codes that are single digit?? not sure...

    if you can use formulas... then CLEAN() will do it..

    as in =CLEAN(A1) then you can copy/paste over original.

    I also did some google.. and it appears function in this thread can work:

    http://www.eggheadcafe.com/software/...-characte.aspx

  9. #9
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Remove Special Characters

    I can live with using =Clean, but is there a way to retain/add the spaces between words?

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove Special Characters

    Maybe

    =TRIM(SUBSTITUTE(A1,CHAR(9)," "))

  11. #11
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Remove Special Characters

    Pure genius. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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