How to display numbers in billion in a cell and that is linked from another cell with currency
This is my current formula for million but I need in billion. Please help thanks
="EGP " + LEFT([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3))) + IF(LEN([Total 2023]57) > 3, "," + MID([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3)) + 1, 3)) + IF(LEN([Total 2023]57) > 6, "," + MID([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3)) + 4, 3)) + IF(LEN([Total 2023]57) > 9, ".")
Answers
-
This is your formula
="EGP " + LEFT([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3))) + IF(LEN([Total 2023]57) > 3, "," + MID([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3)) + 1, 3)) + IF(LEN([Total 2023]57) > 6, "," + MID([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3)) + 4, 3)) + IF(LEN([Total 2023]57) > 9, ".")
That part in bold says that if the length of the number is greater than 9 digits (i.e. 1 billion or more) to put in the first 9 digits and a dot at the end. The result would be this:
To extend this, you can replace the part in bold that is adding the dot with some more IFs.
Your current formula only converts the number on row 57. Is that correct? In this example I have made it work for the current row. If you want to make this static, you can replace "@row" in my example with 57.
I have set this up to work up to 1 trillion. If the number is 1 trillion or more the formula puts three dots in the middle of the number to let you know. This is the input on the left and the result of the formula on the right.
The new formula is here, the new part is in bold:
="EGP " + LEFT([Total 2023]@row, IF(MOD(LEN([Total 2023]@row), 3) = 0, 3, MOD(LEN([Total 2023]@row), 3))) + IF(LEN([Total 2023]@row) > 3, "," + MID([Total 2023]@row, IF(MOD(LEN([Total 2023]@row), 3) = 0, 3, MOD(LEN([Total 2023]@row), 3)) + 1, 3)) + IF(LEN([Total 2023]@row) > 6, "," + MID([Total 2023]@row, IF(MOD(LEN([Total 2023]@row), 3) = 0, 3, MOD(LEN([Total 2023]@row), 3)) + 4, 3)) + IF(LEN([Total 2023]@row) = 10, "," + RIGHT([Total 2023]@row, 3), IF(LEN([Total 2023]@row) = 11, "," + RIGHT([Total 2023]@row, 3), IF(LEN([Total 2023]@row) = 12, "," + RIGHT([Total 2023]@row, 3), IF(LEN([Total 2023]@row) > 12, ",..." + RIGHT([Total 2023]@row, 3)))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!