If A2 contains North and you want the result to display as ‘North’, enter this formula in B2: ="'"&A2&"'". Excel uses double quotes to mark text inside a formula; the single quotes between them are ordinary characters that the formula joins to the cell value. Single quotes also have a separate role in worksheet references, so the right method depends on what you want to quote.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The formulas below work in all three; ribbon paths are not needed.
- A worksheet and a cell containing the text you want to use. The examples use A2 as the source and B2 as the formula cell unless stated otherwise.
- A clear idea of whether you want quote marks to appear in the result, an apostrophe within text, or quotes around a worksheet name in a reference. These are different tasks.
Step-by-Step
- Choose what the single quote should do. If you want the visible result to have a single quote at its beginning and end, you are wrapping text. If you want a word such as O’Brien, you are inserting an apostrophe within text. If your formula refers to a sheet named Sales Data, the single quotes are part of the worksheet reference syntax. Identify the goal first so you do not add quote marks in the wrong place.
- Put the source text in a cell. For the wrapping example, enter North in A2. Click B2, where you want the quoted result to appear. You can enter the formula directly in the cell or select the cell and type it in the formula bar. A formula cell should not be formatted as Text if you expect Excel to calculate it.
- Wrap the cell value with single quotes. With North in A2 and the formula going in B2, enter
="'"&A2&"'". Press Enter. B2 displays ‘North’. The first text piece is one single quote, the second piece is the content of A2, and the last text piece is another single quote. The ampersand joins those pieces into one text result. - Check that Excel is calculating the formula. Click B2 and look in the formula bar. It should contain the formula, while the cell itself shows the result. If the cell shows the formula characters instead, check that the cell is not set to Text and that the formula begins with an equals sign. Changing the format to General and re-entering the formula usually resolves this.
- Fill the formula down for more rows. If A2:A10 contains the text to wrap and B2 is the first result, use the formula from the previous step in B2, then copy or fill it down through B10. The relative reference changes with each row, so B3 uses A3, B4 uses A4, and so on. Check one result near the bottom to confirm the source and output rows stayed aligned.
- Use CHAR when you prefer a character code. With the source value in A2 and the result in B2, enter
=CHAR(39)&A2&CHAR(39). CHAR(39) returns the straight apostrophe character, so this produces the same visible result as the earlier formula. This version can be useful when a formula has several quote marks and you find the character-code approach easier to read. - Put an apostrophe inside a text result when needed. If A2 contains Morgan and B2 should say It’s Morgan, use this formula in B2:
="It's "&A2. The apostrophe is just a character inside a text string, which Excel marks with double quotes. If you want a fixed text value such as O’Brien without using a cell, enter it as a formula with double quotes around the whole string:="O'Brien". - Quote a worksheet name when you refer to it. In a formula on another sheet, single quotes can enclose a sheet name that contains spaces or other characters that make quoting necessary. For example, if a sheet is named Sales Data and you want its B2 value, enter
='Sales Data'!B2. The single quotes belong around the sheet name, and the exclamation mark separates that name from the cell reference. This formula does not add visible quote marks to the value returned. - Handle an apostrophe in the worksheet name. If the worksheet is named Bob’s Data and you want its A1 value, write the apostrophe in the sheet name twice within the quoted reference:
='Bob''s Data'!A1. Excel uses the doubled apostrophe to represent the one apostrophe that is actually part of the sheet name. Do not confuse this sheet-name escaping rule with wrapping a cell’s text in visible single quotes. - Build a worksheet reference from a cell only if you need a variable sheet. If A2 contains an existing worksheet name such as Sales Data, and you want the value from B2 on that sheet, enter this in C2:
=INDIRECT("'"&A2&"'!B2"). INDIRECT turns the assembled text into a reference. Use it only when the sheet name needs to come from a cell; a direct reference is simpler and less fragile when the sheet is fixed.
Common Mistakes
Using single quotes to delimit formula text. Excel formulas use straight double quotes to mark a text string. A single quote inside those double quotes is treated as the character you want to display. For example, in the wrapping formula, the double quotes delimit each text piece while the apostrophe is the piece itself. Typing a single quote as if it were the text delimiter will not create the intended text formula.
Expecting a worksheet-name quote to appear in the result. In a reference such as ='Sales Data'!B2, the quotes identify the worksheet name as part of the reference syntax. They are not added to the value in B2. To display quote marks around that value, use a separate wrapping formula that refers to the sheet cell.
Forgetting to double an apostrophe in a sheet name. A worksheet name such as Bob’s Data has an apostrophe inside it. In a direct reference, that apostrophe must be doubled while the sheet name is enclosed in single quotes. If you use just one, Excel may report a formula problem or fail to interpret the reference correctly.
Typing an apostrophe before the equals sign. A leading apostrophe entered before a formula tells Excel to treat what follows as text. It is not the same as adding a quote character to the calculated result. If the goal is to calculate and display a quoted value, enter a formula that joins the characters to the cell content instead.
Using curly quotation marks in a formula. Formulas should use straight keyboard quotes, not typographic curly marks copied from a word processor or webpage. Curly marks may be treated as different characters and can cause a formula to be rejected. The apostrophe generated by CHAR(39) is a straight apostrophe, not a curly typographic mark.
Leaving a formula cell formatted as Text. When a cell is set to Text, Excel may display an entered formula literally instead of calculating it. Change the cell format to General, then re-enter the formula. Merely changing the format after entry may not make Excel recalculate the text already stored in the cell.
Tips
- Keep the source text unchanged and build the quoted version in a separate column. That makes it easier to reuse the original value in sorting, matching, or other calculations.
- Use the ampersand method for a small number of text pieces. It is short, works in current Excel versions, and does not require a special function for adding apostrophes.
- Use CHAR(39) when the formula has many double quotes and separating the apostrophe character makes the formula easier for you to inspect. Both approaches return text.
- Remember that a formula-generated quoted result is text. The quote marks are part of the displayed text, not Excel number formatting, so the result may not behave like a number or date in later calculations.
- For worksheet references, use a direct reference when the sheet is known. INDIRECT can make the sheet name dynamic, but the text it constructs must match an existing sheet and cell address. A renamed or deleted sheet can break the reference.
- If you share the workbook, check that the apostrophe style is intentional. Straight apostrophes and curly apostrophes look similar in some fonts but are different characters and may not match when searched or compared.
Troubleshooting
The formula is visible in the cell instead of the result. First check whether the cell is formatted as Text. Set it to General, then edit the cell and press Enter to make Excel interpret the entry as a formula. Also check whether Show Formulas is enabled; pressing Ctrl+` on Windows toggles formula display. On many Mac keyboards the equivalent key combination is Control+`; keyboard layouts can differ, so you can also toggle Show Formulas from the Formulas tab.
Excel shows an error after you enter the formula. Count the straight double quotes and check that each text piece begins and ends correctly. Make sure the formula starts with an equals sign and that each ampersand joins complete pieces. For a sheet reference, verify the worksheet spelling, the exclamation mark, and the doubled apostrophe if the sheet name itself contains one. If you pasted a formula, replace curly quotes with straight keyboard quotes.
The result has extra apostrophes or is missing one. Inspect the formula in the formula bar and count the apostrophe characters used as text pieces. The wrapping formula needs one apostrophe before the cell value and one after it. If the source cell already contains apostrophes, those will remain too; remove or clean them from the source only if that matches your data requirement.
INDIRECT returns a reference error. Check that the sheet name in A2 is spelled exactly as it appears on the worksheet tab, including spaces and punctuation, and that the referenced cell exists. A2 should contain only the sheet name, not extra quote marks. Also remember that INDIRECT will not follow a reference to a closed external workbook. If the sheet is fixed, replace the dynamic formula with a direct worksheet reference.
The formula works on one computer but not another. Excel installations can use different regional list separators, though the examples here do not use a multi-argument function. If a workbook has been edited with different regional settings, check the formula as Excel displays it on that installation. Use straight apostrophes and double quotes, and make sure the worksheet name and reference are valid in the workbook.
Frequently Asked Questions
How do I put single quotes around text from a cell?
Join one apostrophe, the cell value, and a second apostrophe with ampersands. With text in A2 and the result in B2, use ="'"&A2&"'". The result is text, for example ‘North’ when A2 contains North.
Can I use a single quote to start a text string in an Excel formula?
No. Excel uses double quotes to mark text strings in formulas. A single quote can be included inside a double-quoted string as an ordinary character, or used around a worksheet name in a reference.
How do I put an apostrophe inside a word or phrase?
Include it as a character inside a double-quoted text string, or join text that already contains the apostrophe. For instance, a formula can return It’s followed by a name in A2. For a fixed name such as O’Brien, the apostrophe goes inside the double quotes.
Why does an Excel sheet reference have single quotes?
Single quotes can enclose a worksheet name when the name contains spaces or characters that require quoting. They mark the sheet name in the reference; they do not add apostrophes to the value returned from the referenced cell.
How do I refer to a sheet name that contains an apostrophe?
Enclose the sheet name in single quotes and double the apostrophe that is part of the name. For a worksheet named Bob’s Data, the reference uses Bob”s inside the enclosing quotes. Make sure the name in the formula matches the worksheet tab.
Will adding single quotes change a number into text?
When you join quote characters to a cell value, the result is text, even if the source cell contains a number. If you need to keep doing arithmetic, calculate with the original numeric cell and create a quoted display result separately.
Related
- Excel Data Validation: Create Rules and Drop-Down Lists
- How to Set Data Validation in Excel for Reliable Entries
- Detect Circular References in Excel: Step-by-Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Count Unique Values in an Excel PivotTableTo count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data…
- How to Use the PMT Function in Excel for PaymentsUse Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a…
- Excel Find and Replace Exact Match: What to KnowTo find or replace a cell only when its entire contents match, press Ctrl+H in Excel for Microsoft…
- Excel OFFSET Function: How to Use It in Formulas and RangesIn Excel for Microsoft 365 on Windows, OFFSET returns a reference moved a chosen number of rows and…
- AVERAGEIF Excel Formula: Examples, Criteria, and TipsIn Excel, use AVERAGEIF to average numbers that meet one condition. For example, with departments in A2:A7 and…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.