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

user_13556265 0
Viewed 120

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

zero concatenation excel-formula excel
1 Answers

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")
user_97581940
Related