## What is the Text Function in Excel?

The TEXT function in Excel converts numbers from the numerical format to text format by using formatting codes. We can use it to display numbers as words, including symbols, while maintaining the numeric value. The TEXT function also helps concatenate numbers to formatted text strings or symbols.

**Example, =TEXT(A6,”dd mmmm, yyyy”)** converts the numerical Date ** 27-01-2023 **into the text format

**27 January, 2023**### Key Highlights

- We use the TEXT function in Excel to convert Dates into a specific format.
- It does not support a fractional format.
- We use the TEXT function to combine numbers with characters or text.
- We cannot combine the time, Date, #, and 0 in the TEXT function formula.
- Excel gives an error #Name if there are no quotation marks around the argument ‘format_text’.

### The Syntax for TEXT Function in Excel

The syntax for the TEXT function is

**Value**(required): It indicates the numeric value we want to convert to text. The value can be a number, Date, reference to a cell containing a numeric value, or any other function that returns a number or Date.**format_text**(required): It is a format you want to apply. We must provide the argument as format code enclosed in quotation marks,**g., “mm/dd/yy”**.

### How to Use the TEXT Function in Excel?

Consider the following examples to understand the use of the TEXT function:

#### Example #1

Consider the below table, which shows a Date in “dd-mm-yyyy” format. We want to convert it into – “mmmm d, yyyy” using the TEXT function.

**Solution:**

**Step 1: **Place the cursor in cell **C6** and enter the formula,

**=TEXT(B6,”mmmm d,yyyy”)**

**Explanation:**

**B6:**It is the cell containing an unformatted parameter (Date)**“mmmm d,yyyy”**: The open and closed double quotes indicate the output in text format.**mmmm**: Four times**m**indicates the month in complete spelling, i.e., January; To write month as only**Jan**, we need to put it three times, i.e., mmm (refer to the below image)

**d:**A single**d**indicates Dates in single digits, i.e.,**2****yyyy**: Four times**y**indicate the year in four digits, i.e.,**2019**

**Step 2: **Press **Enter **key to get the below result

#### Example #2

The table below shows the time and Dates in columns B and C, respectively. We want the values of the time and Date columns to appear together in column C.

**Solution:**

**Step 1: **Place the cursor in cell D6

**Note:**If we use the CONCATENATE function to combine the values of columns B and C, it gives time in an incorrect format, as seen in the image below. Therefore, we need to use the TEXT function to provide the values in a proper form.

**Step 2: **Enter the TEXT formula,

**=TEXT(B6,”h:mm:ss AM/PM”)& ” “&TEXT(C6,”m/d/yyyy”)**

**Explanation:**

**h:mm:ss AM/PM:**It indicates the desired time format**&” “&:**It acts as a separator between the two text**m/d/yyyy:**It indicates the desired Date format

**Step 3: **Press **Enter **key to get the below result

The **result **is combined time and Date values in single cell D6.

**Step 4: **Drag the formula in other cells to get the below result

#### Example #3

The below table shows the scientific notation of a list of mobile numbers with their country codes. We want to use the TEXT function to convert the mobile numbers into the correct and readable format.

**Important:**In a Microsoft Excel worksheet, when we enter a value with the number of digits greater than 11, Excel converts that value into a scientific notation in a non-readable format. We can use the TEXT function to convert it into a readable format.

Follow these steps to understand this-

**Step 1: **Place the cursor in cell D6 and enter the formula,

**=TEXT(C6,”############”)**

**Explanation:**

**C6:**It is the cell containing the mobile number**############:**The 12-digit**#**codes convert numbers in scientific notation into readable format.

**Step 2: **Press **Enter** key to get the below result

**Step 3: **Drag the formula in other cells to get the below result

**Tip:**We can differentiate the country code from the mobile number by inserting a hyphen (-) at the desired location in the Text Formula

So,

**Step 4: **Modify the formula by inserting a hyphen (-) after two digits in the formula, as shown below,

**Step 5: **Drag the formula in other cells to get the below result

### Using Concatenate and Text Function in Excel Together

We want to display the present day and Date using the TEXT function.

**Solution:**

Place the cursor in cell B5 and enter the formula,

**=CONCATENATE(“Today is”,” “, TEXT(TODAY(),”dddd d mmm, yyy”))**

**Explanation:**

**TODAY():**Indicates the present day of the week**dddd d mmm, yyy:**Indicates the current Date with the day.

The formula combines (concatenates) the text **Today is **with the present day to give the result- **Today is Wednesday 25 Jan, 2023**.

### How to Get the Format Codes for Text Function in Excel?

To locate the format codes for **time**:

**Right-click** the desired cell** >** Go To **Format Cells >** **Number >** **Custom >** Select the desired time **Type **from list **>** **OK.**

To locate the format codes for the **Date**:

**Right-click** the desired cell **>** Go To **Format Cells **> **Number **> **Custom **> Select the desired Date **Type **from the list > **OK.**

**Shortcut:** To open **Format Cells,**

- Press
**CTRL+1**in windows, and **+1**in Mac

### List of Format Codes for TEXT Function in Excel

The Text function accepts most format codes used in Excel number formats. Below is a table with the most common and frequently used format codes.

*In addition, we can include any of the below characters in the format code, and they will be displayed as entered.

### Format Codes used in Excel while dealing with Dates

### List of Built-in Excel Text Functions

Below is a list of built-in Text functions in Excel with their uses

### Things to Remember

- The Text function in Excel cannot convert numbers from 123 formats to One Two Three. To do so, we have to use Visual Basics (VBA).
- The Text function converts a numeric value to a formatted text; therefore, we cannot use the result for calculation purposes.
- The ‘format_text’ argument in the text function formula cannot contain an asterisk character (*).
- TEXT function is available in the following versions of Microsoft Excel- Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.

### Frequently Asked Questions (FAQs)

#### Q1) What are the arguments of text function?

**Answer: **The TEXT function takes two arguments-** value** and **format_text**.

**Value**: It is the number we need to format as text. The function will not apply a format code if the argument is already text.

**format_text: **It is a text string (format) that we want to apply to the **value. **We must provide the argument in quotation marks to give the correct result.

#### Q2) How do you use the text function in sheets?

** Answer: **To use the TEXT function in Google sheets, consider the below example

- Place the cursor in the cell where you want the result
- Type the formula,

**=TEXT(A4,”HH mm”)**

The formula will convert the time format from 12 Hrs format (**4:15:00 ****PM**) to 24 Hrs Format (**16 15**)

#### Q3) What is the use of text functions in Excel?

**Answer: **The TEXT function lets you convert numbers into a more readable format. We can change the appearance of numbers to the desired forms by applying the format codes in Excel.

#### Q4) How to use text function in excel?

**Answer: To understand using the TEXT function, consider the below example,**

- Place the cursor in cell B6
- Type the formula,

**=TEXT(A6,”0%”)**

The formula will convert the decimal 0.565 to the percentage format as 57%.

### Recommended Articles

The above article is a guide to using Text Function in Excel. To learn about more Excel-related concepts, EDUCBA recommends the below-given articles.

16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion

4.8

View Course

Related Courses