Number of digits not being preserved in LEFT formula
Hi!
I am probably coming at this all wrong, but am trying to combine a three digit number with a string of text but when I tried a simple addition formula any trailing zeros in the number were lost. I attempted to use a LEFT function to preserve the zeros by indicating the number of digits present, but that still resulted in all trailing zeros being lost.
Example: the cell with a number added is formatted as 9.00 in a column titled ABV (%)
When put in a formula like this (=[ABV (%)]@row+"% ALC/VOL") the resulting text reads 9% ALC/VOL
When using the LEFT function it looks like this (=LEFT([ABV (%)]@row, 4) + "% ALC/VOL") the resulting text reads 9% ALC/VOL
What I want the resulting text to read is: 9.00% ALC/VOL
So ultimately, the same result. I feel like this is a simple issue with a simple answer, but my googling skills are off today.
Best Answer

Hi @Katy Hall
Ah, thank you for clarifying. Ok in that case I think I've come up with a different idea.
In your formula, you could check to see if it will find a decimal... if it can't (or if the values after the decimal = 0) we can add in ".00" as text after the number:
=IF(FIND(".", [ABV (%)]@row) = 0, [ABV (%)]@row + ".00" + "% ALC/VOL",
otherwise, if there are number after the decimal, add your formula as you had it before:
[ABV (%)]@row+"% ALC/VOL")
Full Formula:
=IF(FIND(".", [ABV (%)]@row) = 0, [ABV (%)]@row + ".00" + "% ALC/VOL", [ABV (%)]@row+"% ALC/VOL")
Does that work?
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Hi @Katy Hall
Your original formula will work if there are any values after the period (ex. 9.23 will show as 9.23% ALC/VOL), however any values that are .00 will convert to just show the full, rounded number without the 0s (as you've found).
The way to bring in the 0's would be to have your initial [ABV (%)] column house the data as text string to begin with, versus a number. You could do this by manually adding a leading apostrophe in front of the numbers (like so: '9.00). Since these values would be stored as Text string, you can add this cell to your other text string and it will keep the 0s. Would this work for you?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Unfortunately, that is a form entry point and I’m fairly certain the people entering in the form will forget to add the preceding apostrophe , thereby breaking the formula.

Hi @Katy Hall
Ah, thank you for clarifying. Ok in that case I think I've come up with a different idea.
In your formula, you could check to see if it will find a decimal... if it can't (or if the values after the decimal = 0) we can add in ".00" as text after the number:
=IF(FIND(".", [ABV (%)]@row) = 0, [ABV (%)]@row + ".00" + "% ALC/VOL",
otherwise, if there are number after the decimal, add your formula as you had it before:
[ABV (%)]@row+"% ALC/VOL")
Full Formula:
=IF(FIND(".", [ABV (%)]@row) = 0, [ABV (%)]@row + ".00" + "% ALC/VOL", [ABV (%)]@row+"% ALC/VOL")
Does that work?
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P thank you! That worked perfectly.

No problem! I'm glad we were able to figure something out. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi There,
I have a similar issue with a HR sheet  How do I keep the 2 decimal places after the . even if the numbers are zeros, in a text string formula?
For example:
We have an form input to request a new contract, using the input data I've set up an automation to a document mapping to generate the contract schedule for new staff that all works fine except when there is a zero after the decimal.
Formula is
="$" + [GROSS WAGE]@row + " Per Hr"
Gross wage in the cell is $27.50
result = $27.5 Per Hr
I'm looking for a formula to display the full $27.50 per hour (or if it was $27, to display $27.00 Per Hr)
Kind Regards
Nathan

Hi @Nathan R
Try this:
=IF(FIND(".", [GROSS WAGE]@row) = 0, "$" + [GROSS WAGE]@row + ".00 Per Hr", IF(FIND(".", RIGHT([GROSS WAGE]@row, 2)) = 1, "$" + [GROSS WAGE]@row + "0 Per Hr", "$" + [GROSS WAGE]@row + " Per Hr"))
This first looks for a decimal. If there is none, it adds ".00". Then it checks to see if there is a decimal, but it's in the last 2 characters of the cell (so 27.5). In this instance it adds an extra 0. Otherwise, it just adds your text to the number.
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!