Blog

text function in excel

The TEXT function in Excel is used to convert a numeric value into text and allows you to display that number in a specific format. This is particularly useful for creating more readable numerical displays, formatting dates, or currency values.

Syntax for TEXT Function

The syntax for the TEXT function is is mentioned below

TEXT(value, format_text)

• value: The numeric value you want to convert to text.
• format_text: The format you want to apply to the number.

Common Usages of the TEXT Function

Here are several examples showing different usages of the TEXT function, along with explanations:

Formatting Currency

You can format numbers as currency mentioning example as below.

=TEXT(1234.56, "$#,##0.00")

Output: $1,234.56

Formatting Percentages

We can Convert a number to a percentage format. Lets understand this with an example.

=TEXT(0.567, "0.00%")

Output: 56.70%

Formatting Dates

You can format dates in various ways.

Example:

=TEXT(TODAY(), "dd-mm-yyyy")

Output: 30-10-2024 (assuming today is October 30, 2024)

Alternative Date Formatting:

=TEXT(A1, "mmmm dd, yyyy") ' Where A1 contains a date

Output: October 30, 2024

Formatting Time

Convert numerical time values to a standard time format.

=TEXT(NOW(), "hh:mm AM/PM")

Output: 08:33 PM (depending on the current time)

Custom Number Formats

You can create custom formats for numeric values.

=TEXT(1234567, "##,##0")

Output: 12,34,567

Combining Text and Number

You can combine text and numbers in a single output.

="Total Sales: " & TEXT(15000.75, "$#,##0.00")

Output: Total Sales: $15,000.75

You can get the working file from below.

You can read more on this from this Link.

Practical Applications

• Reporting: When generating reports, combining formatted text with numeric values can enhance readability.
• Dashboards: Display key performance indicators in a user-friendly format.
• Data Export: When exporting data, ensure that it is properly formatted for further analysis or presentation.

Conclusion

The TEXT function in Excel is a versatile tool that enables users to customize how numbers, dates, and times are displayed. Its capability to format numbers into readable strings makes it essential for reporting and data presentation.