How To Remove Quotation Marks In Excel

When you add text in a formula in Excel, you take to enclose the text with quotation marks ("…").  This is useful for when you want to create a dynamic string that changes based on some weather.  Simply what happens when you desire to put quotation marks as role of the formula's result?  In this post, I'll be talking most how to put quotation marks in Excel formulas.

Let's accept a expect at an example that nosotros tin can piece of work with.

And let's as well say we want to create a elementary formula that concatenates (or puts together) the Title and what a newspaper company is maxim almost it.  Withal, we're going to as well say that we want to put any the quote from the NY Times is inside quotation marks, so how would we do that?

Hither are some possible options you can use.

Use Two Unmarried Quotes

This is not the best pick, but youcould use ii single quotes similar so:

            ="NY Times calls the movie "&A2&" ''"&B2&".''"          

If nosotros break downwards this formula, it looks similar the following:

            ="NY Times calls the picture " &A2& " ''" &B2& ".''"          

The first line starts off with the beginning of the static text that we have, which is wrapped by quotation marks.  Then it combines the value in A2 (by using the ampersand &) which is Some random title.  Followed by more static text, simply this time it is a infinite followed by two unmarried quotes.  And then the value constitute in B2 which is astonishing.  And finally, a period and then another set of single quotes.

This results in:

            NY Times calls the moving-picture show Some random championship ''amazing.''          

While this does work, it'south just cheating if you enquire me :)

Does this commodity help you? If so, delight consider supporting me with a java ☕️

Put Quotation Marks Inside the Data

This is another clever, but cheap try at getting the solution we want, but itdoes work.  We have to alter the information to enclose each quote within quotation marks like so:

And the formula would exist:

            ="NY Times calls the moving picture "&A2&" "&B2&"."          

This results in:

            NY Times calls the movie Some random championship "amazing".          

At present we have the menses on the exterior of the quotes, which isn't then bad.  And yet again, while this method does work, seriously, who wants to get through all of their information and put quotes around each prison cell's value (yes, yes, I know at that place are easy VBA hacks for this, but that'due south outside the scope of this weblog post (psst…if yous want me to mail well-nigh VBA, delight annotate below!)).

Come on, Joseph! Get to it already!

Alright, fine :)

Calculation Quotation Marks in Formulas

The magic to putting quotation marks in an Excel formula to have them testify up as quotation marks in the outcome is actually in the quotation marks themselves!  Confusing?  Distressing.  This example should clear information technology up:

            ="NY Times calls the moving-picture show "&A2&""""&B2&"."""          

Let's divide this up and take a closer look:

            ="NY Times calls the movie " &A2& " """ &B2& "."""          

This time around, to create the quotation marker, you put two quotation marks together inside the text portion of the formula.  The previous formula results in:

            NY Times calls the movie Some random title "amazing."          

That'south much meliorate.

If that case is a petty disruptive, check this i out:

            ="I said it was ""crawly"" not ""awful""!!!"          

This results in:

            I said information technology was "awesome" not "atrocious"!!!          

If you lot notice, the entire text is surrounded by quotation marks.  This tells the formula that everything within those surrounding quotation marks is text.  When Excel runs into the two quotation marks together, information technology processes it as a single quotation mark in the upshot.

Wow, you read the whole commodity! You know, people who brand it this far are true learners. And conspicuously, you value learning. Would yous like to larn more than nearly Excel? Delight consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Written by Joseph who loves teaching most Excel.

Source: https://www.spreadsheetsmadeeasy.com/how-to-put-quotation-marks-in-excel-formulas/

Share this post