CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
El_Birdo
El_Birdo

Reputation: 325

google sheets formula to add commas to a number

Objective

To get the values returned from the below formula to display with commas i.e. 429489281 would become 429,489,281. I've been able to find was how to format the cell if this was a standard number but I haven't been able to find something to say how this could be done within a formula itself. Can someone confirm if there is a simple approach to do this so my values take the "number" format, without decimals?

Cells

B6 = 429489281
B17 = 605036489

Formula

="The total number of delivery in Q1 was "&B6&" compared to "&B17&" in Q4 "&"["&ROUND(((B6-B17)/B17)*100)&"%]."

Current Output

The total number of impressions tracked in Q1 was 429489281 compared to 605036489 in Q4 [-29%].

Desired Output

The total number of impressions tracked in Q4 was 429,489,281 compared to 605,036,489 in Q4 [-29%].

Upvotes: 4

Views: 9679

Answers (2)

L. Scott Johnson
L. Scott Johnson

Reputation: 4382

Use TEXT to format the number ="The total number of delivery in Q1 was "&TEXT(B6,"#,0")&" compared to "&TEXT(B17,"#,0")&" in Q4 "&"["&ROUND(((B6-B17)/B17)*100)&"%]."

Upvotes: 4

urdearboy
urdearboy

Reputation: 14580

In excel you would use TEXT formula:

...." & TEXT(B6,"#,0") & " compared to " & TEXT(B7, "#,0") & " in Q4....

Upvotes: 11

Related Questions