TEXT Function in Excel

Text Function in Excel

Text function in excel is categorized under string or text function in excel and this is a very useful function which is used to change a given input to the text provided a specified number format, this formula is used when we large data sets from multiple users and the formats are different for each other.

Text Formula

Below is the TEXT Formula in excel:

TEXT Formula

value: that we want to format

format_text: format code that we want to apply

How to Use TEXT Function in Excel? (with Examples)

TEXT function in Excel is very simple and easy to use. Let us understand the working of TEXT in excel by some examples.

You can download this TEXT Function Excel Template here – TEXT Function Excel Template

Example #1

For example, we have time and dates in column A and B, and we want both the values to concatenate with space as a separator.

TEXT Function Example 1

So, we want to display both time and date together in column C to get values together as

7:00:00 AM 6/19/2018, 7:15:00 AM 6/19/2018, and so on.

Now, when we try to concatenate both the values, what we get are the values shown below:

TEXT Function Example 1-1

You can see the Excel displayed the values of date and time, but this format is not clear and readable for the user because whenever we enter a date into a cell, Excel formats the date using the system short date format, and when we concatenate both the Excel display the system value for both date and time.

To make it more clear, readable, and in the desired format, we will be using the TEXT function.

For the time, we want to display it as hours:minutes:seconds AM/PM and date as Month/Date/Year

Excel provides a list of custom formats and the format that we want. We can check, opening format cells window.

Press crtl+1 in windows and   +1 in Mac to open up the Format Cells window. In the Number tab, go to Custom.

TEXT Function Example 1-2

Scroll down and check for the formats required.

TEXT Function Example 1-3

Under Type: copy the format for date(m/d/yyyy) and time(h:mm:ss AM/PM)

TEXT Function Example 1-4

Now, in C2, we use the TEXT function in Excel, which takes two arguments the value and the format code that we want to apply to that value. So, the TEXT formula in Excel becomes

=TEXT(A2,”h:MM:SS AM/PM”)&” “&TEXT(B2, “m/d/yyyy”)

Dragging the text formula excel to other cells, we get the desired output in the format that we want.

TEXT Function Example 1-5

Note: The format code has to be double quotes else; the TEXT function in Excel will generate an error #NAME?

Example

If we don’t surround the date format with double quotes, it will result in an error shown below.

TEXT Function Example 1-6

Example #2

Excel Text function can also be used for the numbers with large values, for example, telephone numbers. When a numeric value in excel exceeds value 99999999999, then excel always represents that value in scientific notation.

TEXT Function Example 2

Suppose we have a list of customers with their mobile numbers in columns A and B. The mobile numbers are given with their country code.

As you can see below,w we converted these values in scientific notation format in excelScientific Notation Format In ExcelIn Excel, scientific notation is a specific style of writing numbers in scientific and exponential forms. Scientific notation compactly helps display values, allowing us to compare and use the same in calculations.read more.

TEXT Function Example 2-1

Now, we want it in a format such that the country code and the mobile number could be easily readable. We can make it readable using the Excel TEXT function.

A mobile number country code contains 12 digits, starting two signifies the country code, and the remaining is the contact mobile number. So, the format code that we use here is “############”

So, TEXT formula in Excel becomes,

=TEXT(B2,”############”)

TEXT Function Example 2-2

Now, if we want to make it more readable by separating the country code, then we will change the format code by simply placing a hyphen just after two hashes.

TEXT Function Example 2-3

Text Function Format Code While Handing With Date

While dealing with dates, there a list of format code given below

TEXT Function Example 2-4

Example #3

Concatenation with String

Suppose we have a list of children and their date of birth

TEXT Function Example 3

Now, we want to display name and date of birth in column C, like John was born on 8 December 2015 and similar for other kids.

If we directly concatenate the name and the date of birth, we get value in a format like this.

TEXT Function Example 3-1

But we want it in another format, so using the Excel TEXT function, we will be able to get the desired output.

So, the format for the date specified, we will use the format code “d mmmm, yyyy.”

Example 3-2

If we want to use the abbreviation of the month instead of a full name, we will change mmmm to ‘mmm.’

Example 3-3

If we want to display a day with a leading zeroLeading ZeroLeading zeros are zeros that are added to figures without altering their mathematical values. This is done to keep a specific format in a sheet or to prevent errors. We may add Leading Zeros in Excel by utilizing the Right, TEXT, and Concatenate Functions.read more, if it is a single value day like (1-9), we will replace ‘d’ with ‘dd.’

Example 3-4

So, depending upon the requirement and the format in which we want to display a value, we can alter the format code. We can also customize the built-in format codes, depending upon the requirement.

Example #4

Suppose we have Gross and expense amount, and we want to display in cell A13, “The net profit is” &B11

Example 4

Example 4-1

When we apply the TEXT formula in Excel,

=”The net profit is “&B11 to B11, we get

Example 4-3

The formula in A13 doesn’t display the formatted number, which is in dollars. So, the revised TEXT formula in Excel that uses the TEXT function to apply to format to the value in A13:

=”The net profit is ” &TEXT(B11,”$#,##0.00″)

This TEXT formula in Excel displays the text along with a nicely formatted value: The net profit is $52,291.00

Example 4-2

TEXT Function in Excel Video

Recommended Articles

This has been a guide to TEXT Function in Excel. Here we discuss the TEXT Formula in excel and how to use TEXT in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *