Unique character in Excel cells - Access import problem

[cdenski]

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.

 

[Mark W.]

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

 

[cdenski]

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

 

[Mark W.]

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

 

[klb]

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.

 

[Mark W.]

<font color=red>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.</font>
This message was edited by Mark W. on 2002-04-24 14:02

 

[Aladin Akyurek]

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.

 

[mar4jc]

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

 

[mar4jc]

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!