Calc: no comma in raw numbers, how to copy to text -with- the comma

English
#1

Hi all
I have some numbers, for example, number, confidence interval low, confidence interval high

County N CI-Lower CI-Upper
Albany 1234 1228 1240

These raw numbers don’t have comma as thousand separator, but I used cell format them so they do. Now they -look- like this.

County N CI-Lower CI-Upper
Albany 1,234 1,228 1,240

I want to make another column of -text- combining all three, but showing the comma, so I’d get this column:

1,234 (1,228 - 1,240)

For some reason when I use concatenate, the comma doesn’t show up.

=CONCAT(C3," (",E3," - “,F3,”)")

I just get the numbers without the commas.

I presume I have to somehow get the raw numbers to have a comma in them, without using cell formatting but I’m not sure how to do this.

Any help would be greatly appreciated.

Thanks

Gene

#2

How about using TEXT function?
=C3&" ("&TEXT(E3;"#,###")&" - "&TEXT(F3;"#,###")&")"

1 Like
#5

Thanks very much @EarnestAl and @keme1. The formats from keme1 work. The formats by EarnestAI are -almost- what I’m looking for, except I wanted the first number with a comma too. But both suggestions are greatly appreciated.

If I modify EarnestAI’s suggestion slightly, this works.
=TEXT(C2,"#,###")&" ("&TEXT(E2," #,###")&" - “&TEXT(F2,” #,###")&")"

#6

Are you sure that you want that format? "#,###" will suppress zero values, so if any of the numbers may be zero, the display of value and confidence interval will be confusing. Consider using "#,##0" instead.

If every value will be in the thousands, this is of course no issue.

1 Like
#7

I don’t see any of the values with the data I have so far have 0, but they might. I revised to “#,##0” and that seems to work too.

#8

Cell formatting is a function that changes the displayed characters without changing the value.
You wanted to include a comma in the value, but doing so would change it into a string that cannot be calculated.
Therefore, everyone has prepared an answer that does not change the cell value.
Please understand that the idea of ​​not using cell formatting is not appropriate.

#3

=TEXT(C3;"#,##0 ")&TEXT(E3;"\(#,##0 ")&TEXT(F3;"\- #,##0\)")

The backslash in the format string “escapes” the potential numeric/formatspec interpretation of the next character, so it is displayed verbatim.

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.