This tutorial explains the capabilities of the Excel TEXT function and shows some handy ways to work with text types in Excel.
Microsoft Excel is all about numbers, and you'll probably deal with numbers most of the time in your worksheets. However, there are times when you need to convert numbers to text, and that's where the TEXT function comes in.
Excel TEXT function - syntax and basic usage
Basically, TEXT in Excel is used to convert numeric values to a text string in a specific format.
The syntax of the Excel TEXT function is as follows:
text ( value , formattedText )
Where:
- value- Numeric value to convert to text. It can be a number, a date, a reference to a cell that contains a value, or some other function that returns a number or date.
- text_form- The formatting you want to apply. provided as aformat codeIt is surrounded by quotation marks, such as "mm/dd/yy".
The TEXT function works in all versions of Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 and earlier versions.
Typically Excel TEXT formulas are used in the following situations:
- Display numbers in a more readable or meaningful format for the user.
- Displays the date in a specific format.
- Combine numbers or dates with some text or characters.
For example, if you want to extract a date from cell A2 and display it in another cell in a traditional date format, such as "January 1, 2016," use the following Excel TEXT formula:
=TEXT(A2, "mmmm d, gggg")
The result will be similar to:
Important tip!TEXT function in ExcelConvert numeric values to text strings.Therefore, you will not be able to use the results of the text type in other calculations. If there is such a need, you can keep the original values (hidden or invisible) and use them in other formulas.
Excel TEXT function format code
As you just saw, writing the Excel TEXT function is very simple, the hard part is providing the correct formatting code that will display your numbers the way you want. Basically, the TEXT function accepts mostExcel number format.The table below lists the most common and most frequently used ones.
| code | describe | code format example |
| 0 | Placeholder for digits showing silly zeros. | #.00 - always displays 2 decimal places. If you enter 2.5 in the indicated cell, it will be 2.50. |
| # | Numeric placeholders that do not display extra zeros. | #.## - Displays up to 2 decimal places. If you type 2.5, it will appear as 2.5. If you type 2.555, it will appear as 2.56. |
| ; | Numeric placeholders that allow space for non-significant zeros but do not display them. Often used to align numbers in decimal columns. | #.?? - Displays up to 2 decimal places and aligns the decimal point in the column. |
| . (perfect) | decimal point | |
| ,(notch) | Thousands separator. | ###,###.## - Displays thousands separators and 2 decimal places. If you type 250000, it will be displayed as 250,000.00 |
Additionally, you can include any of the following characters in the format code and they will appear exactly as typed.
| symbol | describe |
| + me - | the plus and minus signs |
| ( ) | left and right parenthesis |
| : | colon |
| ^ | input symbol |
| ' | apostrophe |
| {} | large props |
| < > | smaller and larger than marks |
| = | equal sign |
| / | notch |
| ! | exclamation mark |
| I | symbol |
| ~ | tilda |
| spatial character |
The following spreadsheet shows how to use text types in Excel to apply different types of formatting to the same value.
| ONE | But | do | |
|---|---|---|---|
| 1 | initial value | formatted value | officially |
| 2 | 5.5 | 5,50 | =text(A2,"0,00") |
| 3 | 550% | =Text(A2,"#%") | |
| 4 | $5,50 | =TEKST(A2,"$#,##0,00") | |
| 5 | + $5.50 | =TEKST(A2, "+ $#,##0,00;- $#,##0,00;$0,00") | |
| 6 | - 5,50 $ | =TEKST(A2, "- $#,##0.00;- $#,##0.00;$0.00") | |
| 7 | 5 1/2 | =TEKST(A2,"# ?/?") | |
| 8 | 5.50E+00 | =Text(A2"0.00E+00") | |
| 9 | ~ 6! | =tekst(A2,"~#!") |
When you use the Excel TEXT functiondatumISECOND CLASS, you can use any of the following formatting codes.
| format code | describe | example |
| Hey | day of the month or Day of the week | Hey- One or two digits (1 to 31) without zeros dd- Two digits with leading zeros (01 to 31) DDD- Three letter abbreviation (from Monday to Sunday) ddd- full name of the day of the week (from Monday to Sunday) |
| rice | month (when used as part of a date) | rice- One or two digits (1 to 12) without zeros mm- Two digits (01 to 12) with leading zeros Hmm- Abbreviated month (from January to December) Ahhh- full name of the month (from January to December) |
| And | Year | eh- Double digit (e.g. 06 for 2006 or 16 for 2016) uhh- Four digits (eg 2006, 2016) |
| H | Sat | H- One or two digits (1 to 24) without zeros Hmm- Two digits with leading zeros (01 to 24) |
| rice | minutes (when used as part of time) | rice- One or two digits (1 to 60) without leading zeros mm- Two digits with leading zeros (01 to 60) |
| But | but | But- One or two digits (1 to 60) without leading zeros SS- Two digits with leading zeros (01 to 60) |
| morning afternoon | Time is expressed as a 12-hour clock followed by "AM" or "PM" | |
The following spreadsheet shows some Excel text types for dates:
| ONE | But | do | |
|---|---|---|---|
| 1 | initial date | date format | officially |
| 2 | 09.12.2016 | December 9, 2016 | =TEXT(A2, "mmm dd yyyy") |
| 3 | Friday, December 9, 2016 | =TEXT(A2, "dddd dd mmmm, gggg") | |
| 4 | December 9, 2016 | =TEKST(A2, "d-mmmm-gg") | |
| 5 | Friday | =TEXT(A2;"X") |
How to use the TEXT function in Excel - formula example
By now, it might seem to you that the use of the Excel TEXT function is very limited (because its usecell formattingdialog accessible via the cell's context menu or Ctrl+1 shortcut). Things change when you start using TEXT with other Excel functions. The following examples will give you some inspiring ideas.
Concatenate text and numbers (or dates) in a custom format
When creating summary tables or reports, you can often not only calculate totals, percentages, etc., but also explain to your users what this or that number means. To do this, useconnection modeCombine text and numbers and the TEXT function to display numbers (or dates) however you like.
Example 1. Formatting numbers into text strings
Suppose you use the following calculation to calculate the total amount based on the unit price in cell A2, the quantity in B2, and the discount percentage in C2:=A2*B2*(1-C2).In order for your users to understand exactly what the output number means, you want to display it with some explanatory text, such as "your honor is". Additionally, you want to display the currency symbol, thousands separator, and two decimal places.
To do this, specify the above calculation in the first parameter of the TEXT function, include the appropriate format code in the second parameter, and use the & operator or the CONCATENATE function to concatenate the text type to the string:
= "Your value is "&TEXT(A2*B2*(1-C2), "$###,###.00")
the
= CONCATENATE("Your value is ",TEXT(A2*B2*(1-C2), "$###,###.00"))
The following screenshot shows the result:
Example 2. Combine text and date in desired format
When you use the TODAY() or NOW() functions to return the current date, you may also want to display it along with some text so that no one is left wondering what day the date refers to.
However, if you try to combine text and dates in the usual way:
=CONCATENATE("Today is", TODAY())
Excel will return a very strange result like "Today it is 42198".
The point is that in the internal Excel system,Dates are stored as numbers, and the number appears in the concatenated text string. To fix this, use the TEXT function to display the date as you want.
For example, embedding the TODAY function in a text type with the format code "dd or mmm, yyyy" returns a string similar to: "Today is Monday, December 12, 2016."
The full formula is as follows:
=CONCATENATE("Today is ", TEXT(TODAY(), "dd or mmm, yyyy"))
the
="Danas je " & TEXT(TODAY(), "dddd d mmm, yyyy")
You can find more examples of formulas here:Match numbers and dates to different formats.
Add leading zeros to variable-length numbers
As you may know, Microsoft Excel automatically removes zeros entered before cell numbers, and this works well in most cases. But what if you want to keep the leading zeros?
The Excel TEXT function can easily solve the problem of adding numbers with leading zeros in a column, even if the leading values are of different lengths. Just use a format code that contains only zero placeholders, such as "0000000", where the number of zeros corresponds to the number of digits you want to display.
For example, to display a 7-digit number with leading zeros, use the following formula (where A2 is the leading number):
=text(A2"0000000")
As you can see in the screenshot below, Excel's text type adds the required number of leading zeros to form a 7-character string (remember, the result of the TEXT function in Excel is always a text string, even if it looks like a number) .
Convert a value to a phone number in a specific format
Converting a column of numeric values to phone numbers may sound like a daunting task, but only if you remember that the Excel TEXT function allows dashes and parentheses in code formatting.
So, to display the number in A2 in traditional US local 7-digit phone format (like 123-456), use the following formula:
=TEKST(A2, "###-####")
If some primitive values may contain native prefixes (ie they may be 7 or 10 digits long), include the followingin conditional formattingEnter a 10-digit number in the format (123) 456-789:
=TEKST(A2,"[<=9999999]###-####;(###) ###-####")
The screenshot below shows this Excel text type in action:
Excel TEXT function not working - causes and solutions
Compared to other Excel functions, TEXT is so simple and painless that you probably won't have any trouble using it in a spreadsheet. If, as anyone would expect, text types aren't working for you, in most cases it's becauserich textdiscussion. Here are the two most typical problems:
- Does the TEXT function return #NAME? An error occurs if the quotes around the format code are omitted.
type example
=text(A2,mm/room/area)wrong, it should be written like this:=TEXT(A2, "mm/day/year") - The TEXT function in Excel isspecific language, and requires the use of locale-specific date and time format codes.
For example, the guy=TEXT(A2, "mm/day/year")It works fine for English users, but may throw a #VALUE error in other languages. Especially in Germany you should use the following format codes:Of"replace"Hey"I"i"replace"AndSince "day" in German is "marka" and year is "jahr", "m" (month) is fine because it also starts with "m" (monat) in German. So in German Excel the above formula would look like this:=TEKST(A2; "mm/hh/gg").In France you would use the format code "mm/yy/yy" because "day" is "jour", "month" is "mois" and "year" is "an".
Also note that in European countries the list separator is usually set to a semicolon, not a comma, hence the "?". Parameters valid for delimited types.
This is how you use TEXT in Excel. If anyone wants to delve deeper into the types discussed in this guide, feel free to download itAn example of the Excel TEXT functionwork sheet.
Other Excel text functions
Besides TEXT, there are some other functions for working with text values in Excel. Some of them are designed to convert text to numbers and vice versa, while others are designed to work with text strings. Below you will find examples of both types of formulas.
- Excel's left function
- Excel function RIGHT
- Excel MID function
- How to convert numbers to text
- How to convert date to text
- How to remove extra spaces in a cell(tweak mode)
- How to replace one or more characters in a text string(replace and replace function)
- How to concatenate text strings in Excel(connection mode)
- How to find the position of a specific character in a string(search and search functions)
- How to change uppercase and lowercase letters in Excel(UPER, LOWER and PROPER functions)