Formatting numbers in a column

Nagma Khan

Hi

I have a series of very large numbers which I would like to add to a column. Is there a way to show numbers e.g. 2000 as 2.00E+3, in Smartsheet.

Thanks

Paul Newcome

It may be possible. Are you able to provide a screenshot with some mocked up/manually entered data that shows exactly what you are trying to accomplish?

Sarah MK

Hello,

I have the same issue and have not been able to find a solution.

Any updates on this matter?

Thanks

Genevieve P.

Hey @Sarah MK

Can you post a screen capture with mock data as Paul noted, and clarify exactly how you want your numbers to be set up?

Thanks!

Genevieve

Sarah MK

This is what it currently looks like

And this is the format we need but the first and third column need to be still able to calculate the third column

Paul Newcome

@Sarah MK I cannot remember the exact number, but I do know that numbers in Smartsheet get converted to text strings when they get too large. I did some very quick testing and things were converted when I got to 17 digits, but that's not to say some numbers that are 16 digits long could be converted into text. @Genevieve P. may know the highest number usable in Smartsheet. I just can't remember anymore.

The reason I mention this is that you need numerical values as opposed to text values to run calculations such as adding and subtracting.

Having said that... Your sample data seems to be within that threshold, so calculations should not be an issue. The issue comes from Smartsheet not having an actual format like that. You would need to plug the numbers in, and we can convert it into a text string that looks the way you want, but they will not be usable for calculations. You would still need to calculate using the full number. They would be for display purposes only.

So to get (for example) the number 5,140,000,000,000 converted into the visible format you are looking for (5.14E+13), we take the leftmost character, add a period, take the second and third characters, add the "E+" then drop in the number of characters remaining (total minus 3).

=LEFT([Column Name]@row) + "." + MID([Column Name]@row, 2, 2) + "E+" + (LEN([Column Name]@row) - 3)

If that isn't what you are looking for exactly, we could set it up the opposite way so that you can type in 5.14E+13 and then in a hidden helper column output the numerical value which would be used in the calculations, but I need to do some testing before I post that formula. I know we could do it with a very long nested IF, but I would want to see if we can make it a little more efficient.

Sarah MK

Thank you Paul!

I do need to use those numbers for calculations so the second option is really what I am after.

Let me know if you find an efficient solution.

Genevieve P.

Paul's response is completely correct! 🙂

In my tests it was 16 digits that's the limit for a numerical value in a Smartsheet cell. Paul is also correct that in order to do calculations with your numbers they would need to stay in numerical format, and Smartsheet does not currently have the format you'd like. Once there's a text character with it, it's seen as text, which means it can't be summed.

Paul Newcome

@Sarah MK Ok. Let me do some testing and get back to you. It definitely won't be today though. Unfortunately today is already pretty full.