An Excel formula that uses text usually fails because the text is not enclosed in straight double quotes, does not exactly match the cell value, or is being treated as part of a text-formatted cell. Check the formula’s quotes and spelling first; then confirm the cell format, comparison rules, and data type using the fixes below.
How Excel handles text in formulas
Excel distinguishes between text, numbers, and formulas. Text typed directly into a formula must be enclosed in double quotes, while a cell reference containing text is written without quotes. For example, in a worksheet where A2 contains the word Paid, this formula returns Done: =IF(A2="Paid","Done","Open"). The layout assumes the status is in A2; the result is Done when A2 contains Paid and Open otherwise.
Putting quotes around a cell reference changes its meaning. "A2" is the literal text A2, not a reference to cell A2. Conversely, leaving a word unquoted can make Excel interpret it as a name, such as a defined name or function, and return an error if no such name exists. A practical rule is to quote words and phrases, but not cell addresses or numeric values.
Text can also be joined with the ampersand operator. With a first name in A2 and a last name in B2, =A2&" "&B2 combines the cell values with a space between them. For example, if A2 is Maya and B2 is Chen, the result is Maya Chen. The layout assumes those two names are in A2 and B2.
Check quotes, spelling, and formula syntax
Excel formula text strings need straight double quotation marks: "text". Curly quotation marks copied from a formatted document or webpage may look similar but are not formula delimiters. Replace them by typing the quotes directly in the formula bar or cell. Also check that every opening quote has a matching closing quote and that commas separate the arguments in the examples here.
Text comparisons depend on the characters actually in the cell. An extra space at the end of a value, a different spelling, or a nonbreaking space copied from a website can make an apparent match fail. To inspect a suspicious value, click the cell and look in the formula bar; you can also widen the column or move to the end of the entry to see trailing spaces. For a direct comparison, with the value in A2, =A2="Paid" returns TRUE if A2 matches Paid and FALSE if it does not. The layout assumes A2 is the cell being checked.
Excel’s ordinary text comparisons are not case-sensitive: Paid and PAID generally compare as equal. If capitalization must match exactly, use EXACT. With the text to test in A2, =EXACT(A2,"Paid") returns TRUE only when the characters and capitalization match Paid. The result is FALSE for paid or PAID. EXACT does not remove spaces or otherwise tidy the text.
Make sure Excel is calculating the formula
A correct formula can appear not to work if Excel is displaying it as text instead of evaluating it. This commonly happens when the cell was formatted as Text before the formula was entered, or when an apostrophe was typed before the equals sign. In either case, Excel may show the formula itself in the cell rather than its result.
- Select the affected cell and, on the Home tab, change the Number Format dropdown to General.
- Enter the formula again. Changing the format alone may not cause an existing text entry to be evaluated.
- If an apostrophe appears before the equals sign, remove it, then confirm the formula with Enter.
If many formulas are visible instead of their results, check whether Show Formulas is enabled. On Windows, press Ctrl+` (the key usually shares a key with the grave accent) to toggle formula display. On Mac, the shortcut can depend on the keyboard layout; use the Show Formulas command on the Formulas tab if needed. Also check Formulas > Calculation Options and select Automatic if the workbook is set to Manual. In Manual mode, results may not update until calculation is triggered.
Text that looks like a number is still text
Imported data often contains numbers stored as text. A cell displaying 125 may contain the text string “125”, not the numeric value 125. That distinction matters: arithmetic and functions such as SUM may ignore text values in a referenced range, while a text comparison will treat them as text. A small green triangle in a cell can indicate a number stored as text, though it is not a definitive diagnosis for every situation.
One simple check is to compare the cell’s behavior with a numeric calculation or inspect its alignment and warning indicator. To convert a numeric text value in B2, use =VALUE(B2). The layout assumes B2 contains text that represents a number, such as “125”; the result is the numeric value 125. VALUE can also interpret recognizable date and time text according to Excel’s regional settings, so confirm the result is the intended value.
For example, suppose B2 contains the number 10, B3 contains the text “20”, and B4 contains the number 30. With those entries in B2:B4, =SUM(B2:B4) returns 40 because SUM ignores text in a referenced range. The layout assumes those three values occupy B2 through B4. Convert the imported text value if it should contribute to calculations, or use Excel’s available conversion tools for a whole column rather than changing cells one by one.
Formatting is not conversion: changing a text value’s number format to Number does not necessarily turn the stored text into a number. Likewise, adding an apostrophe before a value intentionally makes it text. Keep numbers as numbers when you need to add, sort numerically, or compare them as quantities; retain text when leading zeros are meaningful, such as in postal codes or product identifiers.
Fix criteria and wildcard mismatches
Functions such as IF, COUNTIF, and SUMIF compare text according to their own criteria rules. In COUNTIF, a criterion can be a quoted text value or a pattern containing wildcards. With descriptions in A2:A10, =COUNTIF(A2:A10,"*East*") counts cells whose text contains East anywhere in the entry. The layout assumes the descriptions occupy A2:A10; for example, East warehouse and Northeast route both match.
The asterisk wildcard represents any number of characters, including none; a question mark represents one character. If you need to match a literal asterisk or question mark rather than use it as a wildcard, prefix it with a tilde in the criterion. If a criteria formula combines a condition with another cell, make sure the comparison operator is inside the quoted part and joined to the cell reference with an ampersand. A criterion intended to mean greater than the value in C2, for instance, is assembled differently from the literal text “>C2”.
COUNTIF and similar criteria comparisons are generally not case-sensitive. If the requirement is a case-sensitive test, use EXACT as part of a suitable formula rather than assuming COUNTIF distinguishes capitalization. When a criterion seems right but returns zero, check hidden spaces, spelling, wildcard placement, and whether the target cells contain numbers or text.
Dates and regional settings can complicate text
Dates are stored by Excel as serial numbers and merely displayed in a date format. A date imported as text can look identical on screen but fail in date arithmetic, sorting, or criteria comparisons. Check whether the cell behaves as a real date by changing its number format temporarily; a real date will display its underlying serial value in a general numeric format. Restore the preferred date format afterward.
Text-to-date conversion can depend on regional settings. For example, a string such as 04/05/2026 is ambiguous: it can mean April 5 or May 4 depending on the date convention. Do not assume Excel will interpret imported date text the way its source system did. When possible, import dates with a clearly specified format or use a conversion process that matches the source data.
Formula argument separators can also vary with regional settings. The formulas in this article use commas, as is common in English-language Excel configurations. If Excel rejects a formula that otherwise appears correct, its expected list separator may differ. Follow the separator Excel accepts in your environment rather than replacing commas blindly; also distinguish a separator issue from a text-quoting problem.
A quick troubleshooting sequence
Work through the checks in order instead of rewriting a long formula at random. First isolate the part that should match the text, then verify the source data and calculation settings. These checks help identify whether the cause is syntax, content, or cell type:
- Confirm the formula begins with an equals sign and the cell is not set to Text.
- Replace curly quotation marks with straight double quotes and check that each text value is spelled exactly as intended.
- Click the referenced cell and look for leading or trailing spaces, an apostrophe, or a value imported as text.
- Test a direct comparison in an empty cell, then check whether case sensitivity or wildcard behavior matters.
- For number or date calculations, confirm that the source entries are real numeric values or dates, not text that only looks the same.
- Check Formulas > Calculation Options if results are stale, and make sure Automatic calculation is selected when appropriate.
For a large imported range, cleaning the source is often more reliable than adding complicated conditions to every formula. Use a helper column to convert values when you need to preserve the original data for checking. Once the conversion is verified, formulas that refer to the cleaned column are easier to audit and maintain.
Frequently Asked Questions
Why does Excel show my formula instead of the answer?
Check whether the cell is formatted as Text, whether an apostrophe precedes the equals sign, and whether Show Formulas is on. Change the format to General if needed, re-enter the formula, and check calculation settings.
Do text values in Excel formulas need quotation marks?
Text written directly inside a formula normally needs straight double quotes, as in “Paid”. A cell reference such as A2 is not quoted; quoting it makes Excel read the characters A2 as text instead of looking at the cell.
Why does my formula return FALSE when the words look the same?
The values may differ by a space, spelling, or an invisible character, or the formula may be checking capitalization with EXACT. Inspect the cell contents and verify that the comparison uses the intended case rules.
Why does SUM ignore numbers imported from a website?
Those entries may be numbers stored as text. Convert them to numeric values before relying on arithmetic; changing their display format alone may not convert the underlying data.
Why does my formula work with commas on one computer but not another?
Excel’s argument separator can follow regional settings. The examples here use commas, but some configurations expect a different list separator. Use the separator recognized by that installation and check for other syntax problems as well.
Related
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
- Excel Number Format: How Many Digits Can It Really Show?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Why Is My SUMPRODUCT Formula Not Working? Checks and FixesA SUMPRODUCT formula usually fails because its ranges do not have matching dimensions, its data contains errors or…
- Excel Not Working Correctly? Causes and Reliable FixesIf Excel is not working correctly, first save a copy of the workbook, note exactly what is failing,…
- Excel Formulas Not Updating Automatically? Fix ItIf Excel formulas are not updating automatically, set calculation to Automatic: go to Formulas > Calculation Options >…
- Excel Formula Not Updating? Fix Calculation and Data IssuesIf an Excel formula is not updating when its input values change, first check Formulas > Calculation Options…
- Excel SUM Formula Not Working? How to Fix ItIf Excel’s SUM formula is not working, check that the formula begins with an equals sign, its range…
- Excel Spill Error: Why It Keeps Happening and How to Fix ItYou keep getting a spill error in Excel when a formula is trying to return multiple results but…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.