# Excel: breaks single quotes



## Zenoxio (Jul 18, 2006)

I typed this in a field:
*'',*

(two single quotes, then a comma)

Excel changes it to:
*',*

(one single quote, comma)

I don't want it to do this, and I can't figure out why and how to stop it from doing this. Any ideas?


----------



## bomb #21 (Jul 1, 2005)

A single quote at the start of a cell is used to force text format. If you must have 2 single quotes at the start, create them as follows:

type *0146* on the Number keypad *while holding down ALT*.

HTH.


----------



## lavazza (May 15, 2006)

Or...

type THREE single quotes, and you'll end up with TWO


----------



## slurpee55 (Oct 20, 2004)

Bomb nearly has the answer here, for if you type in 3 single quotes, although you will only see two, if you put your cursor in that cell you will see all 3 in the formula bar at the top. However, what you need to do is type in Alt+0146 followed by ', .


----------



## bomb #21 (Jul 1, 2005)

ALT+0146 *per* single quote was what I meant. My bad.


----------



## slurpee55 (Oct 20, 2004)

Hey, one mistake in 3548 posts isn't too bad


----------



## OBP (Mar 8, 2005)

:up:


----------



## lavazza (May 15, 2006)

2 arguments against the Alt+0146 approach:

''' = 3 key strokes
 = 10 keys strokes (12 on a keyboard without a 'numeric keypad')

My eyes AND the underlying logic in Excel (and OpenOffice Calc) say: *'' <>  *


----------



## Zack Barresse (Jul 25, 2004)

Technically they are apostrophe's. 

Excel does this to allow the user to assimilate textual data (i.e. a textual formula, number or date) in a calculated atmosphere. If you are first typing an apostrophe you must always assume (because it will) the first apostrophe will force the cell value to be textual, _edit: thus the first apostrophe will not show in the cell value, but rather be hidden_. For example, compare these two cell entries...

=TEXT("abcd","@")

'=TEXT("abcd","@")

The two will show quite dissimilar results. If you do not know what they would show, put them in two separate cells. (Remember the single apostrophe on the second example.)

Also, the alt code should be 0039 for the single apostrophe. 

(Hiya Bomb/Loche!)


----------



## bomb #21 (Jul 1, 2005)

I tried 0039 before posting & it still did the "Excel changes it" thing -- for me, anyways.

Good to "see" you Zack. :up:


----------



## lavazza (May 15, 2006)

firefytr said:


> Technically they are apostrophe's.


I take it the  was a nod to the grocer's apostrophe


----------



## Zack Barresse (Jul 25, 2004)

I thought it was pretty witty.


----------



## Zenoxio (Jul 18, 2006)

[EDIT] I have a bigger problem. If I use the ALT trick, it doesn't work outside Excel. Excel shows *'',* which is fine. But when I copy that exact thing into PuTTy (to insert sql), it shows up as *.',* which is wrong.

Old problem:
Now I'm having trouble. I don't know how, but two single quotes ended up in a bunch of fields. The Fx bar shows *''text',* while the spreadsheet shows *'text',* so I wanted to use REPLACE to change it to *'text',* but it doesn't seem to be working.

This is what I tried: (where H11 is the cell the text is at)

```
=REPLACE(H11,1,1,"&#8217;")
```
Yet the result of that is *ext',* and I don't know why. I told it one letter from the start. Instead, it's taking 3 letters from the start (two single quotes and the t)...


----------



## Zack Barresse (Jul 25, 2004)

Use the SUBSTITUTE() function instead. Leave out the last syntax (optional).

I.e. =SUBSTITUTE(A1,"'","")

HTH


----------

