Edit - Super User

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Rev

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

| Show 2 more comments

Correct minor typos or mistakes
Clarify meaning without changing it
Add related resources or links
Always respect the author’s intent
Don’t use edits to reply to the author

create code fences with backticks ` or tildes ~
```
like so
```
add language identifier to highlight code
```python
def function(foo):
print(foo)
```
put returns between paragraphs
for linebreak add 2 spaces at end
_italic_ or **bold**
indent code by 4 spaces
backtick escapes `like _so_`
quote by placing > at start of line
to make links (use https whenever possible)
<https://example.com>
[example](https://example.com)
<a href="https://example.com">example</a>

formatting help »
answering help »