How do I get the =LEFT function in excel, to also take the number zero as the first number?

Solution 1:

If all the room numbers are meant to have 4 digits, and the four digits are achieved by formatting numbers less than 1000 with custom format "0000", then you can use

=LEFT(TEXT(A1,"0000"),2)

Edit: FWIW, this approach also works if the value is text.

Solution 2:

It seems you store room numbers as numbers, this case LEFT is not the best choice, use INT instead:

=INT(A2/100)

To keep the leading zero (04 instead of 4) you need to set number format of the cells to "Custom" with "type": 00

Thanks @fixer1234 for the comment!

Solution 3:

This sounds like it's a cell formatting issue. Make sure your Room Number column is formatted as Text and not General or Number.

This is from LibreOffice, but the behaviour is the same for Excel as long as the cell is formatted as Text.

Based on your comment that you simply custom formatted the value to show a leading 0, you can use this formula to check if there is a leading 0 on a room name and then add one if there isn't:

=IF(LEFT(A1,1)=0,LEFT(A1,2),CONCAT("0",LEFT(A1,1)))

Solution 4:

Michael Frank figured out what's going on. A custom format of "0000" adds leading zeros as necessary to display four digits, but it doesn't alter what is in the cell. If you apply that format to 102, the cell will display 0102, but if you use LEN() to test the number of characters, that will show three. You can use this for a solution with a short formula.

In A1, I've got the value 102. In A2 is the same value, custom formatted 0000. The formula in B2 is:

=LEFT(REPT("0",4-LEN(A2))&A2,2)

This subtracts the actual length of the value in A2 from 4, then creates the resulting count of leading zeros and concatenates that to the actual value in A2. Then it takes the left two characters of the result.

Row 3 shows the result when the value in column A is actually four characters.