Using TEXT and &TEXT - display numbers with commas, underline text
 

I'm familiar with the TEXT function, but sometimes a number will display
incorrectly when I format numbers to display with a comma if 4 digits or
more. For example, TEXT(F1,"0,0") will display 10 thru 999 correctly and
displays 1000 correctly as 1,000, but displays 1 thru 9 as 01, 02, etc.

Also wondering how to format part of a TEXT formula to underline, and bold
or color selected words.

Answer: Using TEXT and &TEXT - display numbers with commas, underline text
 

To display numbers with commas using the TEXT function, you can use the following formula:

1. =TEXT(F1,"#,##0")

This will display numbers with commas for thousands and millions, but will not display decimal places. If you want to display decimal places as well, you can modify the formula like this:

1. =TEXT(F1,"#,##0.00")

This will display numbers with commas for thousands and millions, and will display two decimal places.

Regarding the issue you mentioned with numbers 1-9 displaying as 01, 02, etc., you can modify the formula like this:

1. =TEXT(F1,"0")

This will display numbers without commas and will display single-digit numbers without leading zeros.

To underline, bold, or color selected words in a TEXT formula, you can use the & symbol to concatenate different parts of the formula. For example, if you want to underline the word "Total" in a formula, you can use the following formula:

1. ="The "&"Total"&" is: "&TEXT(F1,"#,##0")

To underline the word "Total", you can add the underline character (_) before and after the word, like this:

1. ="The "&"_Total_"&" is: "&TEXT(F1,"#,##0")

To bold or color selected words, you can use the same approach, but with different formatting codes. For example, to bold the word "Total", you can add the bold formatting code (*) before and after the word, like this:

1. ="The "&"*Total*"&" is: "&TEXT(F1,"#,##0")

To change the color of the word "Total", you can add the color formatting code before and after the word, like this:

1. ="The "&"Total"&" is: "&TEXT(F1,"#,##0")

Note that the color formatting code is enclosed in square brackets and the color name is written after the opening bracket. You can use different color names to change the color of the text.

Using TEXT and &TEXT - display numbers with commas, underline text
 

Maybe you want:

=text(f1,"#,###")
or
=text(f1,"#,##0")

If this doesn't help, maybe you could post the values and what you want to see
for a few examples.


Gary wrote:

I'm familiar with the TEXT function, but sometimes a number will display
incorrectly when I format numbers to display with a comma if 4 digits or
more. For example, TEXT(F1,"0,0") will display 10 thru 999 correctly and
displays 1000 correctly as 1,000, but displays 1 thru 9 as 01, 02, etc.

Also wondering how to format part of a TEXT formula to underline, and bold
or color selected words.

--

Dave Peterson

Using TEXT and &TEXT - display numbers with commas, underline text
 

On Sat, 19 Dec 2009 15:45:01 -0800, Gary
wrote:

I'm familiar with the TEXT function, but sometimes a number will display
incorrectly when I format numbers to display with a comma if 4 digits or
more. For example, TEXT(F1,"0,0") will display 10 thru 999 correctly and
displays 1000 correctly as 1,000, but displays 1 thru 9 as 01, 02, etc.


See Dave's response

Also wondering how to format part of a TEXT formula to underline, and bold
or color selected words.

That is not possible with the TEXT function.

That is only possible using a VBA macro to output a text string to the cell,
and format what you wish. But more information is needed to help you implement
that
--ron