Number of digits not being preserved in LEFT formula

Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Katy Hall
    Katy Hall ✭✭✭
    Options

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • Katy Hall
    Katy Hall ✭✭✭
    Options

    @Genevieve P thank you! That worked perfectly.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem! I'm glad we were able to figure something out. 🙂

  • Nathan R
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!