Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Unique character in Excel cells - Access import problem

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I sometimes get a unique character in cells that looks like a single quote. It isn't something I've typed, it just shows up. I've tried using find and replace to get rid of it, but get an error message saying excel can't find any matching data to replace.

    Does anyone know what this is, how to avoid it and how to get rid of it? I need to get rid of it because any record that has it wont import into Access.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It might be a "smart quote". If this is a left-most character use...

    =CODE(LEFT(A1))

    ...to determine its ANSI code.

    Use...

    =CODE(RIGHT(A1))

    ...if its a trailing character.

    Once you've determined the code use...

    =SUBSTITUTE(A1,CHAR(code),"")

    ...to get rid of it.

    [ This Message was edited by: Mark W. on 2002-04-24 12:34 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Where do I place the formula to determine the code use, just in any cell that contains the character? How do I then replace all of them. And it is a left most character, like this:

    'Maxine

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 12:51, cdenski wrote:
    Where do I place the formula to determine the code use, just in any cell that contains the character? How do I then replace all of them. And it is a left most character, like this:

    'Maxine
    If 'Maxine is in cell A1 I'd enter =CODE(LEFT(A1)) in B1 (you might need to insert a new column). =CODE(LEFT(A1)) in this case returns 39. So, insert a new column C and enter =SUBSTITUTE(C1,CHAR(39),"") into C1 to get "Maxine". You can now Copy the contents of column C and Paste over column A using the Edit | Paste Special... Values menu command.

    Of course if a single left-most character was you only concern you could also use the formula, =RIGHT(A1,LEN(A1)-1), in C1 instead of =SUBSTITUTE(C1,CHAR(39),"").

    [ This Message was edited by: Mark W. on 2002-04-24 13:00 ]

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The ' character is probably an old left-aligned label symbol from Lotus 123. And Excel can't find or replace them.

    You can use =trim(yourcell) to fix it. Like the previous suggestion, insert a column, enter the formula, copy, paste special values to replace your orignal text.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cdenski, klb's posting makes sense. After re-reading your initial posting it occurred to me that you probably have the "Transition navigation keys" option checked on the Transition tab displayed by the Tools | Options... menu command. Uncheck it... and the single quotes (') that were not entered using the keyboard will disappear.

    [ This Message was edited by: Mark W. on 2002-04-24 14:02 ]

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-24 13:18, klb wrote:
    The ' character is probably an old left-aligned label symbol from Lotus 123. And Excel can't find or replace them.

    You can use =trim(yourcell) to fix it. Like the previous suggestion, insert a column, enter the formula, copy, paste special values to replace your orignal text.
    Or, just:

    =A1&""

    where A1 houses such an entry.

    'Maxine should not be visible in the cell where it is, but just in the Formula Bar for the suggestion to work.


  8. #8
    New Member
    Join Date
    May 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique character in Excel cells - Access import problem

    Hi Gang,
    This thread answered the exact problem that I was having with leading single quotes.

    However, after using Mark W.'s solution { (=SUBSTITUTE(C1,CHAR(39),"") }, Excel was adding the ' back as I copy and pasted special -> values back to the original cells. I worked around this by adding the following to Mark's equation:

    =SUBSTITUTE(C1,CHAR(39),"")+0

    The addition of zero "tricked" Excel into formatting as a number instead of as text. I was then able to copy my new "numerical" values back into the original cells.
    Regards,
    mar4JC

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique character in Excel cells - Access import problem

    Actually, I just checked and the substitution formula is not really necessary. Just create a column with the following formula:

    =A1+0, where A1 is the cell containing text with the leading single quote

    This will trick Excel into formatting the cell as a number instead of text. You can then copy paste special -> values back to the original cells.
    Easy!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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