How to display numbers in billion in a cell and that is linked from another cell with currency

nicole.tupaz

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, ".")

KPH

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)))))