Excel - """"

M

MG

What is the logic behind ="""" & ="r"

When we need to have "1" in a cell, we need to type = """"&1&""""

Your input will be appreciated
 
J

JE McGimpsey

If you only want to have

"1"

in a cell, then just type it in that way. No need for a formula (i.e.,
don't start it with a '=').

However, if you want a formula, you need to account for XL using "x" as
an indication that x should be treated as Text. If x is omitted, you get
the null string

""

To differentiate between the quotation marks at the beginning/end of a
string and quotation marks that are part of a string, XL's syntax
requires that the latter be doubled:

"this "" will display a quotation mark within this string"

If the quotation mark is the only text within the string, then it looks
like

""""
 
R

Rick Rothstein \(MVP - VB\)

To differentiate between the quotation marks at the beginning/end of a
string and quotation marks that are part of a string, XL's syntax
requires that the latter be doubled:

"this "" will display a quotation mark within this string"

If the quotation mark is the only text within the string, then it looks
like

""""
Click to expand...

To add to JE's comment, consider the following....

="AAA""BBB"

which will displays this...

AAA"BBB

Now, remove the 3 A's to get this modified string...

="""BBB"

which displays this...

"BBB

Finally, remove the 3 B's from the modified string to get this...

=""""

which will display the lone quote mark you asked about. As you can see,
there is nothing magical about the 4 quote marks.... it is just what is left
over when you remove all the text from around the double quote marks needed
to display an internal single quote mark,

Rick
 
A

AdamV

So to get the literal
"1"
the original poster wanted is possible very simply using ="""1""" rather
than weird concatenation he was using
 
R

Rick Rothstein \(MVP - VB\)

So to get the literal
"1"
the original poster wanted is possible very simply using ="""1""" rather
than weird concatenation he was using
Click to expand...

Well, that is true if the 1 is hard-coded; but if the "1" were picked up
from a reference in another cell, then the full concatenation would be
required. For example, if A1 contained 1 as its contents, then another cell
displaying it quoted would need to use =""""&A1&"""". The main purpose
behind my post was to give insight as to why four quote marks in a row are
required to display a single quote mark... remember, my post was a follow-up
one to the last part of JE McGimpsey's posting and, by extension, a response
to MG's statement - What is the logic behind =""""? Specifically, that
statement about the four quote marks to display one quote mark is a question
that comes up from time to time over in the newsgroups devoted to the
compiled versions of Visual Basic; so I adapted my stock answer there for my
response here.

Rick
 
Top