Return to Question

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?

Click to see a sample table [1]: https://i.sstatic.net/EWhBj.jpg

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)

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)

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?

Click to see a sample table [1]: https://i.sstatic.net/EWhBj.jpg

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)

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)