Formating a concatenate function

[Yepper]

Hello. When using the "&" character to join a text (say, in cell A1) with a value from another cell, is there a way to format the value in cell A1 after it has been joined with the text? For example, is there a way to format the number in cell A1 to appear as (1,000), instead of the default -1000? Thanks for you help!

 

[Corticus]

Use the Text formula:

TEXT(value,format_text)

say you want a comma,

&TEXT(+D6,"#,###")

Corticus

 

[Yepper]

Thanks, Corticus. Can you tell me what the format code is when you want to show a negative number with parentheses, instead of a minus sign? For example, (1,234) instead of -1234. Thanks again...

 

[Yogi Anand]

On 2002-05-10 08:27, Yepper wrote:
Thanks, Corticus. Can you tell me what the format code is when you want to show a negative number with parentheses, instead of a minus sign? For example, (1,234) instead of -1234. Thanks again...

Hi Yepper:
You can use FORMAT|CELLS|NUMBER and from the dialog box pick (1234.00) and click OK.

 

[Yepper]

Hello, Yogi. Can you tell me what the "format code" (such as #,###)is that will enable me to have a negative number shown with parentheses and commas? I need the format code itself because I need to use it in conjunction with a Text formula and a concatenate function. Just using the dialog box selection, as you mentioned, won't work in that case. Thanks...

 

[jeffmfrank]

_(* #,###_);_(* (#,###)

 

[Yogi Anand]

On 2002-05-10 09:51, Yepper wrote:
Hello, Yogi. Can you tell me what the "format code" (such as #,###)is that will enable me to have a negative number shown with parentheses and commas? I need the format code itself because I need to use it in conjunction with a Text formula and a concatenate function. Just using the dialog box selection, as you mentioned, won't work in that case. Thanks...

Hi Yepper:
for negative numbers to be represented in parenthesis, the Custom format component is
;(#,###)
the semicolon says this custom format applies to negative numbers, the very first entry before the semicolon is reserved for positive numbers, and then the parenthesis says the negative number will be shown in parenthesis; and the ,### says that we are going to use the comma format -- meaning that thousands will be separated by commas.

Hope This Helps!

 

[Corticus]

A good way to tell what to put in the parens for your text formula:

Click on a cell that has an unformatted number on it that is similar to the format the data will have,
select format cells,
go to the number tab,
select custom,
this will give you all the different formats, as well as showing what the number will look like once formatted.
Hope that helps,
-Corticus