0

If I have a lot of numbers in thousands and I don't want to use regexp to append to them three zeros to see the real number how can I proceed? I didn't found yet a formatting solution or any other solution.

The data sets are companies Balance Sheets like this: enter image description here

Here is an editable example: https://docs.google.com/spreadsheets/d/1sU8k0YBZc4IJc0qMl3qRkLc5E2rOAZ2E18i-xtxqt5I/edit?usp=sharing

What I found is in Excel: https://www.extendoffice.com/documents/excel/3524-excel-add-trailing-zeros-to-number.html

4
  • Welcome to Web Applications Stack Exchange. Consider sharing a publicly editable sample spreadsheet with realistic-looking data. Dec 25, 2021 at 20:40
  • I added an editable example to my question Dec 25, 2021 at 23:34
  • You said I added my sample I see that is more accurate whats is my issue. I'm sorry, I don't see that "your issue" is any more accurate than previously. The only difference that I can see between the answer by @doubleunary and others compared to your spreadsheet is that on your "Solution" sheet the cell content is not formula-driven but actual values. Of course, you could achieve this by using either answer, then accessing the menu "Copy > Paste special > Values only". If you have a more specific issue than this, then you need to edit your question and describe the issue in more detail.
    – Tedinoz
    Dec 27, 2021 at 4:29
  • @Tedinoz in my first question version wasn't added image & spreadsheet sample. So after that, I added them I hope was more accurate what is my issue. The Solution sheet was created by doubleunary. Thank you for your feedback. Dec 27, 2021 at 6:57

2 Answers 2

1

To show the report in full numbers, Insert > Sheet and put this formula in cell A1 of a new sheet:

=arrayformula( 
  if( 
    isnumber(Sheet1!A1:Z) * (row(Sheet1!A1:Z) > 2), 
    1000 * Sheet1!A1:Z, 
    Sheet1!A1:Z 
  ) 
)

The formula fills the whole sheet automatically, multiplying every number below row 2 by one thousand, and returning other values as is.

To show the multiplied numbers in the same sheet in another column, use this formula in row 2 of a free column:

=arrayformula(A2:A * 1000)

To show the original value on rows where there is no number, use iferror(), like this:

=arrayformula( iferror( 1 / A2:A * 1000 ^ -1, A2:A ) )

See your sample spreadsheet.

5
  • I have companies Balance Sheets like this: nasdaq.com/market-activity/stocks/tsla/financials (with more than 10 years - I added an image to my question also). In this case, is still easier to copy all the data in a notped++ and solve it by regexp, than use the formula that you suggested - that is perfect for one column of data as I understood. Thank you for your response Dec 25, 2021 at 23:13
  • @ArnoldVakaria Please don't be dismissive of this answer - it is sound AND adjusts to multiple columns. =arrayformula(A2:A * 1000) is easily edited to =arrayformula(A2:B * 1000) (which processes numbers in both Column A AND Column B). Also, if you have raw data on, say, "Sheet1" then the formula can be edited to create "updated" values on, say, "Sheet 2" - that is, raw data is intact but you have a "view" that is dynamically updated. Also, please recognise that you are providing more information than was in your original question; the author may well have a solution to your revision.
    – Tedinoz
    Dec 26, 2021 at 1:02
  • @Tedinoz I didn't want to be dismissive I just want to summarize what and how I understood. My understanding wasn't complete as you already reflected that I could use the array formula also for the sheets - thanks for the idea. Sure, I also recognized that I added more info (image, example sheet) - it's visible in my answer comment. After that I got the response I upvoted the answer, but because I don't have enough reputation it doesn't have any effect. Naturally, I will wait until doubleunary will get the sample suggested and when I will get the simplest solution I will accept that Dec 26, 2021 at 2:19
  • No worries. As the author of the question, you may not be able to upvote but if a particular answer is helpful then you can accept it.
    – Tedinoz
    Dec 26, 2021 at 5:18
  • Thank you @doubleunary, I like the solution that you made in my sample file. I accepted your answer Dec 27, 2021 at 6:47
0

This answer is offered simply to show that there are many ways of answering this question. This is no better nor worse that the answer by @doubleunary.

  • Let's assume the data is on "Sheet1"
  • Create a new "Sheet2"
  • Enter this formula in Cell A1 of Sheet2

=query({Sheet1!A1:A5,Sheet1!C1:D5},"select Col1, Col2*1000, Col3*1000 where Col1 is not null label Col2*1000 '', Col3*1000 '' format Col2*1000 '#,###',Col3*1000 '#,###'",2)


Sheet1

sheet1

Sheet2

sheet2


Obviously this example is hand-written based on a small number of columns, but this can be expanded to dynamically reference many columns by adapting Google Sheets Query sum indeterminate number of columns from StackOverflow (and for which there are several examples in WebApps).

2
  • Thank you for your response. After that, I added my sample I see that is more accurate whats is my issue and I'm getting more specific answers Dec 26, 2021 at 2:21
  • I added my sample to see more accurate whats is my issue - I want to write something like this Dec 27, 2021 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.