Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel: breaks single quotes


(!)

Zenoxio's Avatar
Zenoxio Zenoxio is offline
Computer Specs
Member with 389 posts.
THREAD STARTER
 
Join Date: Jul 2006
Location: Albany, NY
Experience: Advanced
02-Jul-2007, 03:15 PM #1
Excel: breaks single quotes
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's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
02-Jul-2007, 05:16 PM #2
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's Avatar
lavazza lavazza is offline
Computer Specs
Senior Member with 125 posts.
 
Join Date: May 2006
Location: NZ
Experience: Intermediate
03-Jul-2007, 01:30 AM #3
Or...

type THREE single quotes, and you'll end up with TWO
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
03-Jul-2007, 08:28 AM #4
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's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Jul-2007, 09:39 AM #5
ALT+0146 per single quote was what I meant. My bad.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
03-Jul-2007, 10:52 AM #6
Hey, one mistake in 3548 posts isn't too bad
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,669 posts.
 
Join Date: Mar 2005
Location: UK
03-Jul-2007, 11:03 AM #7
lavazza's Avatar
lavazza lavazza is offline
Computer Specs
Senior Member with 125 posts.
 
Join Date: May 2006
Location: NZ
Experience: Intermediate
03-Jul-2007, 07:24 PM #8
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's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Jul-2007, 05:02 PM #9
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's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Jul-2007, 05:10 PM #10
I tried 0039 before posting & it still did the "Excel changes it" thing -- for me, anyways.

Good to "see" you Zack.
lavazza's Avatar
lavazza lavazza is offline
Computer Specs
Senior Member with 125 posts.
 
Join Date: May 2006
Location: NZ
Experience: Intermediate
04-Jul-2007, 05:17 PM #11
Quote:
Originally Posted by firefytr
Technically they are apostrophe's.
I take it the was a nod to the grocer's apostrophe

Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
05-Jul-2007, 12:37 PM #12
I thought it was pretty witty.
Zenoxio's Avatar
Zenoxio Zenoxio is offline
Computer Specs
Member with 389 posts.
THREAD STARTER
 
Join Date: Jul 2006
Location: Albany, NY
Experience: Advanced
06-Jul-2007, 10:05 AM #13
[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)
Code:
=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)...

Last edited by Zenoxio; 06-Jul-2007 at 10:19 AM..
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
06-Jul-2007, 11:29 AM #14
Use the SUBSTITUTE() function instead. Leave out the last syntax (optional).

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

HTH
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2