The result is the number 1021 formatted as text "1021". All numbers in column D are formatted as text with the formulas seen in column F.
Normally, you want to maintain numeric values in Excel, because they can be used in formulas that perform numeric calculations. However, there are situations where converting numbers to text makes sense. One example is when you want to concatenate (join) a formatted number to text. For example, "Sales last year increased by over 15%", where the number .15 has been formatted with a percent symbol. Without the TEXT function, the number formatting will be stripped. Another example is when you want to perform a lookup on numbers using wildcards, which can't be done with numeric values.
Convert with formatting
The TEXT function can be used to convert numbers to text using a given number format. In the example shown, the TEXT function is used to convert each number in column B to a text value using the formula and number shown in column F. The TEXT function accepts a number as the value argument, the number format to use as the format_text argument. The number format can include codes for dates, times, numbers, currency, percentage, and so on, as well as custom number formats. Notice the output from the TEXT function is a text value, which is left-aligned by default in Excel.
Convert without formatting
To convert a number in A1 to a text value without number formatting, you can concatenate the number to an empty string ("") like this:
=A1&""// convert A1 to text
With the number 100 in cell A1, the result is "100".
In this example, the values in column A are "stored as text". This means if you try to SUM column A, you'll get a result of zero. The VALUE function will try to "coerce" a number stored as text to a true number. In simple cases, it will just work...
In this example, the goal is to get and display the month name from any given date. There are several ways to go about this in Excel, depending on whether you want to extract the month name as text, or just display a valid Excel using the month name...
The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By applying a number format like "00", "000", "0000", you can "pad" numbers with as many zeros as you like. Zeros will only be added where needed...
The Excel TEXT function returns a number in a given number format, as text. You can use the TEXT function to embed formatted numbers inside text.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.