Search code examples
excelexcel-formulaconcatenationzero

Combine two numbers in excel while making sure the second has two digits

I have a formula that takes part of a number and transforms it before stitching the number back together:

B1=RIGHT(A1;2)*0.2
=LEFT(A1;LEN(A1)-2)&B1

Values of B1 are expected to be one or two digits long and represent part of a time code.

The trouble is that if B1 is a single digit number the time code is wrong.

Ex:

A1=14345
B1=9

Result: 1439
Desired Result: 14309 (1:43:09)

I really want to avoid using

=IF((RIGHT(A1;2)*0.2)<10;0&RIGHT(A1;2)*0.2;RIGHT(A1;2)*0.2)

because in reality the formula can become considerably more complex and having recurring elements in it significantly reduces my ability to read and troubleshoot it.

In short, can I use some function to make sure a number is always two digits long even when the first one is zero when when concatenating two numbers.

Many thanks, Johnson

Solution

  • You could use TEXT() to format the single digit into two digits.

    =TEXT(9,"00")

    In your case this could look like:

    =TEXT(RIGHT(A1,2)*0.2,"00")

    Or as a whole:

    =LEFT(A1,LEN(A1)-2)&TEXT(RIGHT(A1,2)*0.2,"00")