Using REGEXREPLACE to insert commas into values in Google Sheets

Question

How do I go about using REGEXREPLACE to insert commas into values in Google Sheets? Example:

cell A1 = 1234567890

expected output in B1 = 1,234,567,890

edit: The intention would be so that the user would not need to format anything themselves. This issue is from a template I'm doing up, sample sheet here - https://docs.google.com/spreadsheets/d/1n-KXqcSpx_DpvrOv9A4UsKn1amGGEUCpyzWWDphWdJY/edit?usp=sharing

player0 · Accepted Answer · 2021-08-31 10:44:23Z

1

try:

=TEXT(A1; "#,###,###,##0")

answered Aug 31, 2021 at 10:44

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

qazamy Over a year ago

Thanks man! Glad to know this was possible without REGEXREPLACE.

qazamy Over a year ago

Any idea on how to incorporate this TEXT() with QUERY()?

player0 Over a year ago

@qazamy QUERY has its own formatting: i.sstatic.net/MdEak.png see: developers.google.com/chart/interactive/docs/querylanguage

qazamy Over a year ago

I can't seem to be able to incorporate it in. Sorry for the trouble. Perhaps you can give it try with this sample sheet - docs.google.com/spreadsheets/d/…

player0 Over a year ago

@qazamy proper syntax would be: i.sstatic.net/QGhfU.png however query is known to be buggy when it comes to mixing text values with numeric values like in your case so try: i.sstatic.net/7qqUg.png

Mike Steelson · Accepted Answer · 2021-08-31 04:18:10Z

0

Try

=A1

and change the format to

#,##0

answered Aug 31, 2021 at 4:18

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

1 Comment

qazamy Over a year ago

This wouldn't be an option as the intention would be so that the user would not need to format anything themselves. This issue is from a template I'm doing up, sample sheet here - docs.google.com/spreadsheets/d/…

alpha con · Accepted Answer · 2021-08-31 04:24:01Z

0

To insert commas into values in Google Sheets, I recommend:

Select the range of cells you'd like to format or modify.
Click Format and then Number.
Select the format to apply to the range of cells.

answered Aug 31, 2021 at 4:24

alpha con

36 bronze badges

1 Comment

qazamy Over a year ago

This wouldn't be an option as the intention would be so that the user would not need to format anything themselves. This issue is from a template I'm doing up, sample sheet here - docs.google.com/spreadsheets/d/…

Collectives™ on Stack Overflow

Using REGEXREPLACE to insert commas into values in Google Sheets

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related