Can someone provide an explanation for how to correctly insert a text quotation mark (qm) into an Excel formula? Obviously there needs to be a way to identify it as not indicating the beginning or end of text included in the formula but part of the text itself.
I've seen solutions here and elsewhere that include four qm's in a row ("""") but I've never understood why. Thus, when I'm confronted with something new, I have a difficult time getting it right.
I can think of only three positions a qm can occupy: at the beginning, somewhere in the middle & at the end of text to be included. For example, suppose you've got data in three columns (B, C & D) as shown here:
And suppose you want to be able to modify the following sentence in three places (underlined) using references to the three columns:
"What's the plan?" I asked. Jack
replied, "I will see Jane
Now, if you will allow me to replace the opening qm's with forward slashes (/) and the closing qm's with reverse slashes (\), it would look like this:
/What's the plan?\ I asked. Jack replied, /I will see Jane in 3 days.\
In that case, the formula would be as follows:
="/What's the plan?\ I asked. "&B4&" replied, /I will see "&C4&" in "&D4&" days.\"
="/What's the plan?\ I asked. "&
" replied, /I will see "&
" in "&
Hopefully all that is not just confusing things but if it makes sense, could you show me the correct way to write the formula and do it in two ways:
• #1 as it would appear with qm's where they should be (i.e. replacing the slashes),
• #2 as it would appear with slashes still present so it's easy to see which of the qm's in #1 are part of the text
— and also include any comments you can that explain why the #1 solution is correct.