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!