Multiple use cases of "TEXT" Function in Excel

This article will describe the features of the Excel "TEXT function" and provide some creative uses for Excel "Text formulas".

The "TEXT function" in Excel is quite versatile and can be used in various scenarios to format numbers and dates as text strings in a specific format.

Formatting Dates:

We can convert a date into the format “day/month, or year.” For example, if in cell A1 we have a date in (14/03/2024) this format, we can convert or extract the day, month, Or year by using the below-mentioned formula.

=TEXT(A1,"DDDD"), If cell A1 has a date (14/03/2024), then this formula will return the day; in this case, it will return "Thursday".

=TEXT(A1,"MMMM"), If cell A1 has a date (13/03/2024), then this formula will return the name of the month; in this case, it will return "March".

=TEXT(A1, "YYYY"), If cell A1 has a date (14/03/2024), then this formula will return the Year; in this case, it will return "2024".

=TEXT(A1, "DDDD MMMM YYYY"), If cell A1 has a date (14/03/2024), then this formula will return the name of the day, month, and full year. In this case, it will return "Thursday, March 2024".

Formatting Numbers with Leading Zeros:?

If cell A1 contains the number 123, the text formula will return the text "00123.".?

It can help when we have a serial number and we need to convert it into a number starting from zero.

For example, if I have serial numbers 9, 10, and 11, for converting them into numbers with a leading zero, we can use the formula written below.

=TEXT(A1,"000"), if A1 contains 9, then the result will be 009.

=TEXT(B1,"000"), if B1 contains 10, then the result will be 010.

Formatting as a Currency

We can format a number in a cell as currency using a "Text Formula".

For example, if we have 10 in cell A1 and need to format it as currency, we can use the formula mentioned below.

=TEXT(A1,"$#,##00"), If A1 contains 10, then the result will be $10.

Combining text and numbers:

We can combine text and numbers in a cell using the TEXT formula.

For example, if we have 10 in cell A1 and we need to add some text message like "Value is,"? we can use the below-mentioned formula:

="Value is"&TEXT(A1,0) If A1 contains 10, then the result will be "Value is 10".

Formatting Aadhar or Any Other No:

Using the text formula, we can format the phone or Aadhar number as we

For example, we have Aadhar No. 1212121212121212.

For formatting these, we can use the formula mentioned below.

=TEXT(A1, "0000 0000 0000 0000"), IF cell A1 contains "1212121212121212" then the result will be "1212 1212 1212 1212"

?

?