Number of digits not being preserved in LEFT formula

Katy Hall

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.

Genevieve P.

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

Genevieve P.

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

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.

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

@Genevieve P thank you! That worked perfectly.

Genevieve P.

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

Nathan R

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.

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