**What Is TEXT Function In Excel?**

The Text function in Excel changes a number to a text string and displays it in a format supplied to the function as an argument. Users can use this Excel inbuilt function to make numeric data more readable or when they require to combine numbers with text or symbols in their spreadsheets.

Let us understand the **Text function in Excel with example**. Below is a table with different numerical values in column A and the required formats are mentioned in column B.

When we provide the individual data and the specific format code as arguments for the** TEXT function in Excel**, we will get the output as shown in the image below. So, we can easily convert numerical values into text using the TEXT function in Excel.

##### Table of contents

- What Is TEXT Function In Excel?
- TEXT() Excel Formula
- How To Use TEXT Excel Function? (With Steps)
- Examples
- Example 1- Prefix The Text Strings To The Newly Formatted Date Values
- Example 2- Join The Newly Formatted Time And Date Values
- Example 3- Extract A Mobile Number From Its Scientific Notation
- Example 4- Prefix A Text String To The Initially Formatted Monetary Value
- Example 5- Add Leading Zeros To Numbers
- Example 6- Converting Decimal To Percentage With TEXT Function
- Example 7- Converting Decimal to Fraction with TEXT Function

- Things to Remember
- Frequently Asked Questions
- Download Template
- Recommended Articles

###### Key Takeaways

- The TEXT function in Excel converts a numeric value into a text string, changing how the number appears in a spreadsheet.
- The formula of TEXT() is
**=TEXT(value,format_text)****value**is the numeric value or cell reference to the numeric value we require to convert into a text string.

- It can format date and time, add text strings to them and monetary values, get mobile numbers from their scientific notations, convert a decimal number into a fraction or percentage and add leading zeros.

**TEXT() Excel Formula**

The syntax of **TEXT function in Excel **is:

where,

**value**: It is the numeric value or cell reference to the numeric value we require to convert into a text string.**format_text**: It is the text string that specifies the format we need to apply to the numeric value.

Unlike other functions, both the arguments mentioned in the **TEXT formula in Excel **are mandatory.

And here are the codes we will typically use in the second argument, **format_text**, to define the required formatting.

Code | Description |
---|---|

0 | The digit placeholder, showing the irrelevant zeros. |

# | The digit placeholder that hides the extra zeros. |

? | The digit placeholder that provides a space for irrelevant zeros, but hides them. It usually aligns numeric values in a column at a decimal point. |

. | Decimal point |

, | Thousands seperator |

Further, the following codes are used when we use the TEXT function in Excel with dates and times.

Code | Description |
---|---|

h | Hour |

m | Minute when mentioned in the time format. |

s | Second |

d | Day |

m | Month when mentioned in the date format. |

y | Year |

AM/PM | Time |

In addition, below are a few symbols we can use in the format code, which will display at the same position in the output text where we place them in the format code.

Symbol | Description |
---|---|

% | Percentage |

$ | Dollar |

& | Ampersand |

/ | Forward Slash |

+, – | Plus and minus signs |

() | Parentheses |

‘ | Apostrophe |

: | Colon |

! | Exclamation Mark |

Space Character | |

= | Equal Sign |

^ | Caret |

~ | Tilde |

**How To Use TEXT Excel Function? (With Steps)**

The steps required to use the TEXT function in Excel are:

- First, enter the numeric values we want to convert into text strings in a spreadsheet.
- Then, type the
**TEXT()**with the first argument as the numeric value or cell reference to the numeric value and the second argument in the required format. - Press
**Enter**to view the numeric value as text data.

Let us understand the steps to apply the **TEXT function in Excel with example**.

The below table shows a number in column A along with a list of different formats in column B. Now, we need to convert the number based on the requirement.

The steps used to format text in excel are:

**Step 1: **To begin with, select cell C2 to display the result.

Next, enter the **TEXT()** as in the Formula Bar in the below image, and then, press **Enter**.

We will get the result as shown in the image below.

The arguments in the** TEXT function in Excel** are cell references to the cell containing the numeric value **20.5** and the format code for the format, **Number with two decimals**. Since the second argument is a text string, it is in double-quotes.

Alternatively, we can choose cell C2 and then go to **Formulas** > **Text** > **Text**.

Next, the **Formula Arguments** window opens.

Now, fill the **Value** and **Format_text** fields and click **OK **to get the **TEXT()** formula in cell C2.

**Step 2: **Then, choose cell C3 and enter the **TEXT()** as in the Formula Bar in the below image.

Press **Enter** key.

Clearly, the first argument remains the same as in the **TEXT()** used in cell C2. But the format code will change as we need to display the numeric value **20.5** as a fraction.

**Step 3: **Choose cell C4 and enter the **TEXT()** as in the Formula Bar in the below image.

Press **Enter** key.

Here the first argument in the **TEXT()** is Excel cell reference A2, and the second argument is the format code to display the value as a percentage.

**Step 4: **Choose cell C5, enter the **TEXT()** as in the Formula Bar in the below image, and press **Enter**.

The text string containing the **$** symbol is supplied as the second argument to the **TEXT()**, making the number **20.5 **display as a currency.

**Examples**

Here are a few illustrations to show how we can effectively utilize the TEXT function in Excel.

**Example 1- Prefix The Text Strings To The Newly Formatted Date Values**

Consider the following table. It shows a list of laptop bags, quantities, and delivery dates in columns A, B, and C, respectively. We need to enter the delivery status as a comment in column D. Let us learn **how to use text function in Excel** with this example.

But first, let us see what the output will be without using the **TEXT()**.

We would choose cell D2 and type the formula,

**=B2&” units of “&A2&” were delivered on “&C2&”.”**

Press **Enter** key.

The above result does not show the date in the required format. Instead, the date gets displayed as a number.

Now let us use the required format for the TEXT formula in Excel to ensure the date exhibited is correct. Here are the steps:

**Step 1: **Choose cell D2 and enter the **TEXT()** with the required prefix as:

**=B2&” units of “&A2&” were delivered on “&TEXT(C2,”dd mmm, yyyy”)&”.”**

And then, press **Enter**.

**Step 2: **Drag the AutoFill handle downwards to copy the formula into cells D3:D6.

While the ampersand concatenates values and text strings, the text within the double quotes appears in the output.

Using them, we can prefix the required text string to the date, formatted using the TEXT function in Excel.

Here, the format code provided as the second argument to the **TEXT()** is **“dd mmm, yyyy.”** The term **dd **shows the day in two digits. And if the day is a single digit, the output will have a leading zero to display the day in two digits (refer to cell D4 in the above image).

Likewise, the code **mmm **indicates the month using the first three letters of the specific month.

And the term **yyyy** shows the year in four digits. But, on the other hand, the spaces and comma symbol appear in the output, as mentioned between the codes.

It is just one date format for the TEXT function in Excel. We can use different date formats according to our requirements.

**Example 2- Join The Newly Formatted Time And Date Values**

The table below shows the time and date values in columns A and B. Now, we need to combine both the values in column C.

**Please Note: **The time and date formats in the source data are **h:mm:ss AM/PM** and **m/d/yyyy, **respectively.

We can select cell A2:A7 and use the shortcut keys **Ctrl** + **1 **to open the **Format Cells** window.

Then, in the **Number** tab, click the **Custom** option, choose the **Type** for the time as **h:mm:ss AM/PM**, and click **OK**.

Likewise, choose cells B2:B7 and set the **Type** field in the **Custom** category as **m/d/yyyy **for the dates.

Now, if we join the time and date values, say in cell C2, the output will be:

The time and date values are not in a readable format. While the time displays as a decimal value, the date is a number. However, the TEXT formula in Excel can help us achieve the required results.

**Step 1: **Choose cell C2. Enter the formula to format the time and date to get readable text and join them.

**=TEXT(A2,”h:mm AM/PM”)&”, “&TEXT(B2,”d-mmm-yy”)**

And then press **Enter**.

**Step 2: **Drag the fill handle downwards to copy the formula into cells C3:C7.

The formula uses the **TEXT()** twice to apply new formats to the time and date values.

The format codes are different from the ones used in the source data.

The **h** and **mm, **in the first **TEXT()** format code denotes the hour and minutes, with minutes in two digits. **AM/PM** gets displayed in the output.

And the **d**, **mmm**, and **yy** in the second **TEXT()** format code show the day, month using the starting three letters of the month and year using the last two digits of the year.

And here again, the ampersand joins the two values in each cell.

**Example 3- Extract A Mobile Number From Its Scientific Notation**

Suppose we enter 12-digit mobile numbers (2-digit extension and 10-digit mobile number) in our spreadsheet. Then the mobile numbers will automatically appear in the scientific notation form within the cells, though the Formula Bar will show the mobile numbers in the original format.

The reason is that Excel can show a number as it is in the cell for a maximum of 11 digits. But if the number contains 12 or more digits, Excel displays it in the scientific notation form.

Let us see how we can resolve this issue **using** **TEXT function in Excel**.

Assume the following table contains mobile numbers.

The steps to get the mobile numbers from their scientific notations using TEXT function in Excel are as follows:

**Step 1: **Add a new column with a header **Mobile Number Displayed Using TEXT()**.

Choose cell B2 and enter the formula, **=TEXT(A2,”############”)**

Press **Enter** key.

**Step 2:** Drag the fill handle downwards to copy the formula in cells B3:B5.

The **TEXT()** format code contains twelve hashes to display the 12 digits of a mobile number.

And if we require to show the 2-digit extension and the 10-digit mobile number separately, the **TEXT()** formula in cell C2 is,

**=TEXT(A2,”##-##########”)**

Once we press **Enter** key and drag the fill handle to copy the formula in cells C3:C5, the output will be:

**Example 4- Prefix A Text String To The Initially Formatted Monetary Value**

Consider the below table with income, expenses, and savings data in columns A, B, and C, respectively.

The three monetary values already have a specific format by setting the **Number Format** in the **Home** tab as **Currency**.

If we need to enter a comment including the total savings from cell C2, then we will enter the formula as shown in the Formula Bar depicted in the below image:

The above formula does not show the initial format of the total savings.

But, the TEXT function in Excel allows us to prefix the required text string to a monetary value while retaining its initial format.

**Step 1: **Choose cell C2 and use the shortcut keys, **Ctrl **+ **1**, to open the **Format Cells** window. Then, go to the** Custom** window to check the applied format since we will require it while supplying the second argument to the **TEXT().**

In this case, it is **$#,##0.00**.

**Step 2: **Select cell D2 and enter the below formula to prefix a text string to the initially formatted total savings value.

The formula is **=”The Total Savings: “&TEXT(C2,”$#,##0.00”)**

**Example 5- Add Leading Zeros To Numbers**

Typically, Excel removes leading zeros that are entered before a numeric value in a cell.

But sometimes, we might want to show the leading zeros, and the TEXT function in Excel enables us to achieve the required outcome.

For example, the below table shows a few numbers.

We can pad each number with leading zeros to create text strings (with 8-characters).

**Step 1: **Name column B as **Text String – Number With Leading Zeros. **

Then choose cell B2 and enter the below **TEXT()** as:

**=TEXT(A2,”00000000″)**

**Step 2: **Drag the fill handle downwards to copy the formula in cells B3:B9.

Depending on the number of digits in each number, the leading zeros get padded to it to become an 8-character string.

However, the last number appears since it already has 8-characters.

**Example 6- Converting Decimal To Percentage With TEXT Function**

We can use the TEXT function in Excel to convert a decimal value into a percentage. The **format_text**, in this case, will be **‘0.00%’ **or **‘0.0%’**.

For example, we need to convert the following decimal numbers into percentages.

The following steps help us understand **how to use text function in Excel**.

**Step 1: **Name column B as **Number In Percentage**.

Then, choose cell B2 and enter the **TEXT()** as:

**=TEXT(A2,”0.00 %”)**

**Step 2: **Drag the fill handle downwards to copy the formula in cells B3:B6.

Each decimal value is multiplied by 100, with the **%** value added at the end.

**Example 7- Converting Decimal to Fraction with TEXT Function**

We can use the TEXT function in Excel to convert a decimal number into a fractional value. The **format_text** we need to supply as the function argument is **“# ???/???”**.

For example, we need to convert the following decimal numbers into fractions.

**Step 1: **First, choose cell B2 and enter the **TEXT()** as:

**=TEXT(A2,”# ???/???”)**

**Step 2: **Next, drag the fill handle downwards to copy the formula in cells B3:B6.

If the decimal number has a non-zero digit before the decimal point, the **TEXT()** with the above format code will return a mixed fraction. Otherwise, the result will be a proper fraction.

**Things to Remember**

- Both the arguments in the TEXT function in the Excel formula are mandatory.
- The second argument in the
**TEXT()**,**format_text**, should be in double-quotes. Otherwise, the function returns the**#NAME?**error. - Ensure the date and time format codes are region-specific.
- Though the
**TEXT()**result might appear as a number, it will be a text string. - The
**TEXT()**takes only one value and one format code as inputs. - Unlike other functions, the TEXT function is an inbuilt function available in all versions of Excel.

**Frequently Asked Questions**

**Where is the TEXT function in Excel?**

The **TEXT function in Excel** is in the **TEXT** function category under the **Formulas** tab.

**Why TEXT function in Excel is not working?**

**TEXT **function might not be working due to the following reasons:

The **format_text** argument value is not in double-quotes.

For example, suppose we enter the required **TEXT()** formula in the target cell as:**=TEXT(B2,# ???/???)**

Then, the function will throw an error.

Instead, the correct way of entering the second argument in the **TEXT()** is:**=TEXT(B2,”# ???/???”)**

We do not use the required region-specific dates and time format in the **TEXT()**.

For example, the format code **m/d/yyyy **will work for Americans. But if the users in France work on Excel with French as the preferred language, then using the format code** m/d/yyyy **in the **TEXT()** will throw an error.

It is because, in French, the day, month, and year equivalents are jour, mois, and an.

And thus, the correct format code we need to use in France is **m/j/aaaa** to make the **TEXT()** work.

**What is the use of TEXT function in Excel?**

The TEXT function in Excel is that we can change a numeric value into text and format it to modify how it appears in a spreadsheet. And using **TEXT()**, we can join numbers with text and symbols, thus making them more readable.

**Download Template**

This article must be helpful to understand the **TEXT Function In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Text Function in Excel. Here we discuss how to use text formula with step by step examples and downloadable excel template. You can learn more form the following articles –

## Leave a Reply