Excel TEXT Function: The Only Guide You Need
Apr 25, 2023Are you tired of writing custom formulas or copying and pasting to format a few cells?
The Excel TEXT function is a useful feature that can help format, clean up, and even combine your data precisely.
From combining several pieces of data into one cell to changing all characters from uppercase to lowercase, there are several functions that you can simplify.
Read on as we explore the following:

TEXT Function in Excel

Excel TEXT Function Syntax

How to Use the TEXT Function in Excel

TEXT Function Format Codes

Final Thoughts on Excel TEXT Function

Frequently Asked Questions on Excel TEXT Function
Read Also: Using IMPORTRANGE to Reference Another Google Sheet
TEXT Function in Excel
The TEXT function in MS Excel converts numbers from the number format to text string format using specific codes. You can incorporate it to show numbers as words or symbols while keeping the numeric value.
The TEXT function also assists in concatenating numbers into formatted text strings or symbols.
Example of TEXT function in date formula:
=TEXT(A2,” dd mmmm, yyyy”) converts the numerical date format (31032023) to the text format (31 March 2023).
Important notes:

The TEXT function's other purpose in Excel is to convert dates into a specific format.

The TEXT function doesn't support any fractional format.

The TEXT function can use to combine numbers with text values.

Excel may show an error #Name if the argument lacks quotation marks.

Excel can't combine the time,#, Date, and 0 in the TEXT function formula.
Excel TEXT Function Syntax
Here's the syntax for Excel TEXT Function:

Value is a number or date that we want to turn into words. It can be a number, date, or something from one of the cells in our spreadsheet. It can also be data that another function generates. i

With format_text, you need to provide the information with a format code. The format code must contain quotation marks like "mm/dd/yy." That tells the program how you want your text formatted.
How to Use the TEXT Function in Excel
Think about the following examples to understand the use of the TEXT function:
Example #1
The table below shows a date in number format. You can use the TEXT function to change those numbers to "March 31, 2023."

Type the TEXT formula.

Press the Enter key.

A2 is the cell with a date in it.

The double quotes mean that Excel will change the data to text format.

If you want to write the month as "March" instead of "Mar," put four m's inside the double quotes.

The letter "dd" represents dates with two numbers, like "31".

The four letters "yyyy" represent the year with four numbers, like "2019".
Example #2
The CONCATENATE function does not work to put together values from columns A and C. It gives time in the wrong way. We need to use the TEXT function instead to get it right.

Type the TEXT formula.

Press Enter and use the Drag fill to fill the other cells in column C.

h:mm: ss AM/PM means the time you want to use.

The symbol "&" "&" goes between two pieces of text.

m/d/yyyy is the date format you want to use.
Example #3
The table displays the scientific notation of a record of mobile numbers with their country codes. We want to apply the TEXT function to convert mobile numbers into a readable format.

Type the TEXT formula.

Press Enter and use the Drag fill to fill the other cells in column C.

Change the formula using a hyphen "" after the first two digits, as shown in the image.

Use the dragfill feature to get the same results in the other cells.

B2 is the cell that has your phone number in it.

The 12digit # codes change numbers written in scientific notation, making them a readable format.
Read Also: How to Do a Search on an Excel Spreadsheet
Combine the TEXT and CONCATENATE Functions
Using TEXT with other Excel functions can make things different. Look at the examples for ideas.

Select a cell in your spreadsheet.

Type and enter the combined CONCATENATE and TEXT formulas.
The formula concatenates the text "Today is" with the present day to give the outcome of "Today is Wednesday, January 25, 2023."

TODAY() specifies the present day of the week

dddd d mmm, yyy: Indicates the current date with the day.
TEXT Function Format Codes
Do the following steps to locate different TEXT function format codes in Microsoft Excel.

Rightclick a cell and select Format Cells.

Within the Format cells dialog box, select Number and Custom. Then, choose a Formatting code, and click the OK button.
Frequently Used Format Codes
You can add special characters to the format code. They will show up exactly how you typed them.
Use the TEXT function when working with dates and times in Microsoft Excel. You can use different codes to format the dates and times differently.
Different Excel TEXT Functions
TEXT function is simply one of the many ways to work with text values in Excel. Some help turn numbers into words and words into numbers, while others help change how the text looks. Here are some examples.

Excel LEFT function

Excel RIGHT function

Excel MID function

Convert numbers to text

Convert date to text

TRIM function

REPLACE and SUBSTITUTE functions

CONCATENATE function

FIND and SEARCH functions

UPPER, LOWER, and PROPER functions
Read Also: How to Solve OLE Action Error in Excel! Why It's Happening with Fixes
Final Thoughts on the Excel TEXT Function
The Excel TEXT Function can help with working on textbased data in their spreadsheets. It can format strings, get parts of a string, combine words and numbers into one cell, and locate characters in a string. Consequently, it becomes easier to deal with data.
For more easytofollow Excel guides and the latest Excel Templates, visit Simple Sheets and the Related Articles section of this blog post.
Subscribe to Simple Sheets on Youtube for the most Excel video tutorials!
Frequently Asked Questions on Excel TEXT Function
Why is the TEXT function in my spreadsheet not working?
The TEXT function is not working because it needs quotation marks, or the formula needs to be accurate.
Can the TEXT function use color formatting?
The TEXT function cannot show colors. If you use a number format code that includes color, the TEXT function will accept it but won't be able to show the color.
What would happen if I put an asterisk in the text argument?
Using the TEXT feature requires that no asterisk be included; doing so will produce an error and prevent the successful completion of the task.
Related Articles:
Excel for Dummies: Everything You Need to Know
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.