Excel - """"

[Guest]

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

 

[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

""""

 

[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

""""

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

 

[Guest]

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

 

[JE McGimpsey]

But why then use a formula at all?

just enter "1"

 

[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

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