Excel TEXT Function: The Only Guide You Need

Are 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 (31-03-2023) 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."

1. Type the TEXT formula.

2. 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.

1. Type the TEXT formula.

2. 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.

1. Type the TEXT formula.

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

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

4. Use the drag-fill feature to get the same results in the other cells.

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

• The 12-digit # 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.

1. Select a cell in your spreadsheet.

2. 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.

1. Right-click a cell and select Format Cells.

2. 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 text-based 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 easy-to-follow 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

How To Insert Shapes In Excel in 6 Simple Steps

How to Add a Button in Excel