We will explain the powerful TEXT function of LibreOffice Calc with examples.
The TEXT function is one of the strong functions used to format numbers. You can easily format the numbers using the formatting structure. In addition, you can also use this function in the cell formula.
In this article, we will explain using various examples.
But before that, let’s take a look at the syntax.
Table of Contents
TEXT Function: Syntax
TEXT(number, format)
- number: The numeric value to be converted.
- format: The format to be used for conversion. It should be enclosed with a double or single quote(s).
- Both arguments are mandatory.
- The output of the function is in text format, obviously.
All the example below takes the first number argument as direct values. You can also use a cell reference such as A1, or B1 in the first parameter, i.e. number.
TEXT Function Examples
Example 1: Show Currency ($) with numbers
The first example, which I will show you, is where you can prepend any currency symbol, add thousands of separators such as command (,) to numbers and add some text.
The below example formats the input number
=TEXT(457888.9,"$#,###0.00")
Returns $457,888.90
=TEXT(-457888.9,"$#,###0.00;-$#,###0.00") //returns -$457,888.90
In the above example, you can see the format is mentioned twice separated by semicolon. If you look carefully you should see they are same and only different is the negative sign. This tells the function to show a minus sign when negative number is present. But when positive number there, don’t show any sign.
If you think where you can find out the format codes ($#,###0.00), well, it’s super easy. It’s the same format codes which you see in the format cells dialog under various categories!
Off to the second example.
Example 2: Format numbers as Millions
One million is 1000,000. So, sometimes it isn’t easy to read numbers with six digits. And it’s easier to present the money in 1 Million or 1 M instead of 1000,000. The TEXT function can do that.
Here are some examples.
=TEXT(51000000,"#,,") & " Million"
The above formula returns:
51 Million
Each comma (,) in the format represents thousands.
Additional examples:
=TEXT(51000000,"#.#0,,") & " Million" // returns 51.00 Million =TEXT(51000,"#,") & " Thousand" // returns 51 Thousand
Do you get the idea? You can easily do for hundreds or billions by combining the # and comma (,).
Example 2: Format as Percentage (%)
The function also rounds off and shows the percentages in a formatted way. For example, if you have a value 75.67% (i.e. 0.7576) in a cell, you can format it to 76% using the below.
=TEXT(0.7576,"0%") // returns 76% =TEXT(0.7576,"0.0%") // returns 75.8%
Example 3: Display Fraction from Decimal
This is an interesting usage of TEXT function. Suppose you have decimal values and you want to find out the fraction. It’s also possible. See below.
=TEXT(0.75,"?/?") //returns 3/4
Moreover, you can always find out the different number formats from the format cells dialog launch from right click.
Example 4: Scientific Notation
For larger numbers, its easier to represent them via the exponent i.e. E values. And TEXT function can help you to do that.
=TEXT(9000000,"0.0E+00") //returns 9.0E+06
Example 5: Format a standard date, make Weekday name and Month name
You can do almost all formatting of dates using this function. For example, if your cell A1 have 8/15/2022 i.e. August 15, 2022, then you can format it as the following.
A1: 8/15/2022 (in mm/dd/yyyy)
=TEXT(A1,"dd-mm-yy") //returns 15-08-22 =TEXT(A1,"dd-mm-yyyy") //returns 15-08-2222 =TEXT(A1,"ddd dd-mm-yy") //returns Mon 15-08-22 =TEXT(A1,"dddd dd-mm-yy") //returns Monday 15-08-22 =TEXT(A1,"ddd mmm dd yy") //returns Mon Aug 15 22 =TEXT(A1,"dddd mmmm dd, yyyy") //returns Monday August 15, 2022
Example 8: Format Time
Similarly, you can also format time on whatever way you want. For example, if I write 23:30 (i.e. 11.30 PM) in cell A1, you can convert it as following.
A1: 23:30
=TEXT(B4,"HH:MM AM/PM") //returns 11:30 PM =TEXT(B4,"HH:MM:SS AM/PM") //returns 11:30:00 PM
Wrapping Up
I hope this guide and examples, gives you a better idea on how to use the TEXT function. It is one of the best and underrated function. Moreover, the above same examples can work well in Excel as well.
Do let me know in the comment box if you have any questions.
Cheers.
