EXCEL formula with the formatted value insted of real value

Question

I have a excel file with a zip codes and I need get the first 2 numbers =LEFT(G2;2);

for this postal codes like 11008 it works, I get 11, but if a postal code has as value 01008 this doesn't works because the unformatted (Real Value) value is 1008

Any ideas?

score 6 · Accepted Answer · answered Jan 08 '14 at 17:04

6

You could turn the zip code first into text using TEXT:

=LEFT(TEXT(G2;"00000");2)

1008 becomes 01008 first, and then you get 01 from the LEFT function.

answered Jan 08 '14 at 17:04

Jerry

70,495
13
100
144

1

I had to use `=LEFT(TEXT(G2,"00000"),2)` for it to work. – SethB Jan 08 '14 at 17:17
@SethB This is because of language and region settings. In some countries, you'd use `;`, otherwise, `,`. – Jerry Jan 08 '14 at 17:18

score 2 · Answer 2 · edited May 23 '17 at 12:03

Your ZIP code column should really be formatted as Text. Even though the ZIP code happens to be represented with numerals, it's not really a number per se.

You don't do math with it;
it could just as easily be represented with letters (and is in some countries);
leading zeros are important.

Same thing with, say, phone numbers.

Here's some relevant questions:

EXCEL formula with the formatted value insted of real value

2 Answers2