Excel Text Usage Examples for Cleaning and Formatting

Excel text examples often use TEXT to turn a number or date into a chosen display, such as =TEXT(A2,"$#,##0.00") for currency. For cleaning, combining, or extracting words, functions such as TRIM, TEXTJOIN, and LEFT are usually more suitable; the right choice depends on whether you want a display, a cleaned value, or a new text string.

What Excel’s TEXT function does

TEXT converts a numeric value into text using a format pattern. Its structure is TEXT(value, format_text): the first argument is the value to display, and the second is a format code in quotation marks. It is useful when a result must appear in a particular style inside a sentence, label, or export file.

For example, a worksheet may store a price as the number 18.5 but need to show it as currency in a sentence. A date may be stored as a date value but need to appear as “Saturday, July 4, 2026.” In both cases, TEXT changes the result into text that follows the requested pattern. The original cell is not changed.

The examples below assume Microsoft Excel for Microsoft 365 on Windows and English-US regional settings. Excel’s date and number display can reflect regional settings, so separators and currency symbols may look different elsewhere. Formulas below use commas between arguments; in some regional configurations, Excel expects semicolons instead.

Format numbers, currency, and percentages

Format patterns let you control decimal places, grouping separators, percentage signs, and leading zeros. With the value 1234.5 in A2, this formula returns the text $1,234.50 under the stated regional settings:

With the number 1234.5 in A2, use =TEXT(A2,"$#,##0.00"). It returns $1,234.50.

The pattern uses a dollar sign, a comma grouping separator, and two decimal places. Use 0 when a digit must always appear, and # when a digit is optional. For instance, with 73 in A2, the following adds leading zeros and returns the text 00073:

With the number 73 in A2, use =TEXT(A2,"00000"). It returns 00073.

That can help format an identifier for display, but it does not make the result a numeric value. If the identifier is important for sorting, matching, or importing, check whether the destination system expects text or a number. A percentage pattern also scales a decimal for display: with 0.125 in A2, =TEXT(A2,"0.0%") returns 12.5%. The stored value remains 0.125.

Display dates and times as readable text

Excel stores dates as serial numbers and times as fractions of a day. TEXT can display those values in a chosen arrangement. If A2 contains the actual Excel date July 4, 2026, this formula spells out the weekday and month:

With the date July 4, 2026 in A2, use =TEXT(A2,"dddd, mmmm d, yyyy"). It returns Saturday, July 4, 2026.

In a date format code, d represents the day, m the month, and y the year. Repeating letters changes the display: dd gives a two-digit day, mmm gives an abbreviated month, and yyyy gives a four-digit year. For example, the same date with dd-mmm-yyyy appears as 04-Jul-2026.

Be careful not to confuse text that looks like a date with an actual date value. If A2 contains characters that Excel has not recognized as a date, TEXT may not interpret them as intended. It is often better to convert or correct the source data first. Also, a TEXT result is no longer a date value for date arithmetic: it will not behave like the original date in calculations.

Combine labels, dates, and values

The ampersand joins pieces of text. When joining a number or date to a label, TEXT gives you control over how that value looks. For example, suppose A2 contains order number 1042 and B2 contains the numeric price 18.5. The formula below produces a single readable label:

With order number 1042 in A2 and price 18.5 in B2, use ="Order "&A2&" — "&TEXT(B2,"$#,##0.00"). It returns Order 1042 — $18.50.

For several cells, TEXTJOIN can add a delimiter and optionally skip empty cells. With the names Ana, Ben, and Carla in A2:A5, and A4 blank, this formula returns a comma-separated list without an extra comma for the blank cell:

With Ana in A2, Ben in A3, a blank A4, and Carla in A5, use =TEXTJOIN(", ",TRUE,A2:A5). It returns Ana, Ben, Carla.

The second argument, TRUE, tells TEXTJOIN to ignore empty cells. Use FALSE if empty cells should count as positions between delimiters. If you only need to join two or three pieces, the ampersand can be clearer; for a range of values, TEXTJOIN is often easier to maintain.

Clean extra spaces and inconsistent text

Imported data often contains extra spaces that interfere with matching or make reports look untidy. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. With A2 containing two spaces before “North,” several between the words, and two after “Shore,” the result is:

With the text North Shore in A2, use =TRIM(A2). It returns North Shore.

TRIM does not remove every character that can look like a space. Web pages and other sources may include nonbreaking spaces, which are not the same as ordinary spaces. One common cleanup approach is to replace character 160 with a normal space and then trim. With A2 containing North, a nonbreaking space, and Shore, the formula below returns North Shore:

With the imported text in A2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). If A2 contains North Shore with a nonbreaking space, it returns North Shore.

SUBSTITUTE replaces specified text, while CHAR(160) represents the nonbreaking space used in this example. Other invisible characters may need a different cleanup method. For data that must match reliably, inspect a few raw cells and test the cleaned result before applying it to a whole column.

Extract or split part of a text string

Use LEFT to take characters from the start, RIGHT to take them from the end, and MID to take characters from a specified position. These functions are useful when values follow a consistent pattern. With invoice code INV-583-CA in A2, the middle three characters are the number:

With INV-583-CA in A2, use =MID(A2,5,3). It returns 583.

The position in MID starts at 1, so the fifth character is the first 5. A fixed-position formula can fail if source strings vary in length or structure. If a code’s pieces are separated by a consistent delimiter, splitting on that delimiter may be clearer.

TEXTSPLIT can split a string into separate cells at a chosen delimiter. It is available in Microsoft 365 and Excel 2024. With the comma-and-space-separated list in A2, it spills the three words into adjacent cells:

With red, green, blue in A2, use =TEXTSPLIT(A2,", "). It returns red, green, and blue in separate cells across the row.

Spilled results need clear cells beside or below the formula. If a cell in the spill area is occupied, Excel displays a spill error rather than overwriting existing content. Older Excel versions may not include TEXTSPLIT; for a one-time task, Text to Columns under the Data tab may be an alternative.

Choose between TEXT and cell formatting

A common misconception is that TEXT merely changes how a cell looks. It changes the formula’s result into text, whereas applying a number format changes only the display of a numeric value. The distinction matters when you later calculate, sort, filter, or use the result in another formula.

Use cell formatting when the value should remain numeric or a date. Select the cells and choose a suitable option from Home > Number Format.
Use TEXT when a formatted value must become part of a text string, such as a label, sentence, or export field.
Use text-cleaning or extraction functions when the problem is spaces, unwanted characters, or a piece of a string—not the display of a number.

For example, if a column contains prices that need to be summed, format the cells as currency rather than converting each one with TEXT. If you are building a message that includes a price, use TEXT within the message formula. Keep a numeric source column when possible, and create a separate display column if both calculation and presentation matter.

Common problems and useful alternatives

If a formula returns an unexpected result, check the source value and format pattern before changing the formula. A number stored as text may not behave like a number; a date-looking entry may be plain text; and a format code may produce a different result under another regional setting. Excel may also show a formula as characters if the cell is formatted as Text or if the formula was entered with a leading apostrophe.

When you need to turn numeric text into a number, VALUE can convert text that Excel recognizes as a number. With the characters 125.4 in A2, the formula returns the numeric value 125.4 under English-US regional settings:

With the text 125.4 in A2, use =VALUE(A2). It returns the number 125.4.

For a formula that should show a formatted result, confirm that the destination cell is not blocking a spill or treating the formula itself as text. If the task is to apply a consistent look to a whole column, built-in number formatting is usually simpler than creating a TEXT formula in every row. If the task is to break data into fields, Text to Columns or TEXTSPLIT may be more appropriate than extracting characters by position.

Frequently Asked Questions

Does Excel TEXT change the original cell?
No. TEXT returns a formatted text result and leaves the source value unchanged. The formula cell contains text, so it may not work like a numeric or date value in later calculations.

How do I keep leading zeros in an Excel number?
For a text result, use TEXT with a zero pattern such as 00000. If the value should remain numeric and only needs to display leading zeros, apply a custom number format instead.

Why does TEXT show a different date or currency format?
Excel can interpret date and number format codes according to regional settings. Check the source value, the format pattern, and the computer’s locale; the displayed currency symbol and separators may differ from an English-US example.

How can I combine text and a formatted date?
Join the label and date with &, and wrap the date value in TEXT using the display pattern you need. This produces a text string, not a date you can use directly for date calculations.

Can TEXT remove extra spaces?
No. TEXT formats numeric values; it does not clean spaces. Use TRIM for ordinary extra spaces, and consider SUBSTITUTE when imported content includes a particular unwanted character.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.