20

My data contains a column of room numbers. Each room number's first 2 digits is the floor number where the room is located. The =LEFT function works well for data that does not start with zero. The =LEFT function doesn't work well for data that starts with zero.

How can I get the formula to not ignore the first number when it is zero?

Room Number       Formula         Floor Number
1214              =LEFT (A2, 2)   12
2354              =LEFT (A2, 2)   23 
1876              =LEFT (A2, 2)   18
1567              =LEFT (A2, 2)   15
0403              =LEFT (A2, 2)   40* (should be level 04)
1918              =LEFT (A2, 2)   19
0910              =LEFT (A2, 2)   91* (should be level 09)
Improve this question
0

5 Answers 5

Reset to default
35

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.

enter image description here

Improve this answer
0
26

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!

enter image description here

Improve this answer
5
  • 9
    Good answer. - I would argue that the "Room numbers" are not really numbers, but more room-identifiers and type text is appropriate. My experience as an API developer has lead me to follow this rule of thumb: "If it doesn't make sense to add or multiply it, it should probably be saved as text rather than a number" - even more true for combined keys where certain "digits" have meaning.
    – Falco
    Commented Aug 7, 2019 at 14:13
  • 2
    This is also the QUOTIENT function: =QUOTIENT(A1,100)
    – Chronocidal
    Commented Aug 7, 2019 at 15:54
  • 3
    @Falco Indeed, it's possible that at some point in the future, a "room number" of "1214A" will exist, and treating what is fundamentally a string as if it were a number will fail.
    – Monty Harder
    Commented Aug 7, 2019 at 16:59
  • my approach works with strings as well as with numbers. I updated my answer with a screenshot.
    – teylyn
    Commented Aug 8, 2019 at 0:16
  • 2
    Int he same category: phone numbers aren't numbers either.
    – MSalters
    Commented Aug 8, 2019 at 12:27
6

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

LEFT formula example
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)))

Screenshot to show leading 0 logic formula in action

Improve this answer
7
  • When I format it as text, the room number 0403 becomes 403. The =LEFT function will grab the number 40 as the floor number.
    – Jefferson
    Commented Aug 6, 2019 at 3:49
  • 1
    You have to enter the room numbers into the cell after you format it to Text, otherwise it will have already dropped the leading 0.
    – Michael Frank
    Commented Aug 6, 2019 at 3:50
  • When I extract the data from the system, rooms located on single digit floors is format as E.g. 405, 905. I added the custom format 0000 thinking that this will let me use the =LEFT function but the function is ignoring the zero in front of it.
    – Jefferson
    Commented Aug 6, 2019 at 3:52
  • 1
    or just =LEFT(TEXT(A1,"0000"),2)
    – teylyn
    Commented Aug 6, 2019 at 5:29
  • 2
    @Jefferson "When I format it as text, the room number 0403 becomes 403." - That's good! Before you formatted it as text, there was a problem (namely, the cell had the wrong contents), but the problem was hidden. Formatting it as text revealed the problem.
    – Tanner Swett
    Commented Aug 6, 2019 at 18:38
6

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.

enter image description here

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.

Improve this answer
2
  • Nice, a much simpler formula than my quick example. +1
    – Michael Frank
    Commented Aug 6, 2019 at 5:03
  • 1
    or simpler: =LEFT(TEXT(A1,"0000"),2)
    – teylyn
    Commented Aug 6, 2019 at 5:29
1

There are already good answers available to your question, but if you want to make this work with the same formula you had used initially:

=LEFT(A1,2)

then just prefix ' (single quote before the number).  See the below screenshot for your reference

e.g., instead of just 0403, make it '0403 and then apply the same formula.

Screenshot for reference below

Add prefix before the number:

Prefixed with '

Works with the same formula:

Works with same formula

Improve this answer
1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .