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?

Jerry · Accepted Answer · 2014-01-08 17:04:58Z

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 8, 2014 at 17:04

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SethB Over a year ago

I had to use =LEFT(TEXT(G2,"00000"),2) for it to work.

Jerry Over a year ago

@SethB This is because of language and region settings. In some countries, you'd use ;, otherwise, ,.

Community · Accepted Answer · 2017-05-23 12:03:47Z

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:

Collectives™ on Stack Overflow

EXCEL formula with the formatted value insted of real value

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related