One-digit and two-digit separation

I have a set of values some of which are one digit (e.g, 5), and some which are two digits (e.g, 55) in cells.

For the calculation I want to make, I want to use the first digit of the values. I don't have a problem with two digits.

How can I make Excel use "0" if it is a one digit number without adding "0" in front of "5" in this example.

I mean I don't want to change the values and add 0 in front of them. How can I do that if it is possible?

Simply, I want excel to use "0" as the first digit, if it is a one-digit number. thanks.

Max

Posted 2017-01-30T09:07:27.470

Reputation: 239

Answers

You can use this formula: =INT(A2/10)

Máté Juhász

Posted 2017-01-30T09:07:27.470

Reputation: 16 807

Thanks a lot! I was trying to do this with =Left() function. I didn't know about =int(). thanks again. – Max – 2017-01-30T09:17:04.667

Máté Juhász's answer using INT is probably the best solution. However, for the sake of curious future readers, there is another, clunkier way:

=VALUE(RIGHT(LEFT("0"&A2,LEN(A2))))

If your data ever goes up to more than two digits and you really want the tens place, not just the first digit, then INT will fail. In that case, you can use this clunky version. If you want to pull the tens place for any number, it gets messier.

=VALUE(RIGHT(LEFT(TEXT(A2,"00"),LEN(TEXT(A2,"00"))-1)))

Engineer Toast

Posted 2017-01-30T09:07:27.470

Reputation: 3 019

2=INT(MOD(ABS(A2),100)/10) - Although depending on use case the abs might not be wanted. – Taemyr – 2017-01-31T11:18:12.683

Use an IF statement since you're only concerned about a different rule if 1 digit (therefore, less than 10).

This is the cell value in C1

=IF(A1<10, 0 + F1, LEFT(A1,1) +F1)

It may seem odd to write 0 + F1 but I'm not aware of what logic you're using so I left it in to demonstrate the formula.

Dave

Posted 2017-01-30T09:07:27.470

Reputation: 24 199