How to use text formula in Excel

August 1, 2022

1.1K Views

A free Office suite fully compatible with Microsoft Office

Free Download

Free download

By using format codes to apply formatting to a number, you can alter how it appears when using the TEXT function. It comes in handy when you wish to blend numbers with text or symbols or display numbers in a more understandable style.

The TEXT function says the following in its most basic form:

=TEXT (Value you want to format, Format code you want to apply)

Here are a few well-known examples that you may copy and paste into Excel to test out on your own. Note the format codes that are enclosed in quote marks.

Formula	Description
TEXT(1234.567,$#,##0.00)	Money that has two decimals and a thousand separator, such as $1,234.57. The value is rounded by Excel to two decimal places.
=TEXT(TODAY(),MM/DD/YY)	Date for today in the MM/DD/YY format, such as 03/14/12
=TEXT(TODAY(),DDDD)	Today is a weekday, just like Monday.
=TEXT(NOW(),H:MM AM/PM	It is currently 1:29 PM.
=TEXT(0.285,0.0%)	percentage, such as 28.5%
=TRIM(TEXT(0.34,# ?/?))	anything like a third. Take note that the leading space before a decimal value is removed using the TRIM function.
=TEXT(1234567898,[	a unique phone number, such as (123) 456-7898

Add Meaningful Words Using Text in Excel Formula

Excel is frequently used exclusively for calculations. As a result, we are not concerned with how effectively they deliver the message to the reader. Take a look at the data below as an illustration.

It is obvious from the above image that we must multiply Units by Unit Price in order to determine the Sale Value.

To calculate the overall sales value for each salesperson, enter some simple text into the Excel formula.

Usually, this is where we stop the procedure.

Maybe Anderson's entire Sale Value should be displayed as 45,657?

It appears to be a whole sentence in order to provide the user a clear message. Let's proceed and construct a sentence using the formula.

Let's proceed and construct a sentence using the formula.

1. We are aware of how the sentence should be framed. First, we require the name of a Sales Person to appear. Therefore, we must choose cell A2.

2. We now require the salesperson's name to be followed by Total Sale Value. To combine this text value, we must add the ampersand operator sign after choosing the first cell.

3. To determine the sale value, we must now perform the calculation. Apply the formula as B*C2 by adding the more (and) sign.

4. To finish the formula and enter our text values, we must now press the Enter key.

The incorrect formatting of the sales statistics in this formula is one issue. That would have made the numbers look right, but they don't have a thousand separator.

There is no need to be concerned; using the TEXT function in the Excel formula, we can format the numbers.

Make formula changes. The computation stage formats the numbers using the Excel TEXT function, as illustrated below.

We now have the numbers in the correct format, along with the sales figures. The Excel formula's TEXT function formats the calculation (B2*C2) as ###, ###.

Did you learn how to use the Excel text function? To discover more about the functionality of Word documents, Excel spreadsheets, and PowerPoint slides, follow WPS Academy. To edit word, excel, and PowerPoint documents for free, you may also download WPS Office. Get it now! and have a pleasant and straightforward working experience.