Extracting 1st 3-digits of postal code that begin with 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to extract the first 3-digits of a postal code, currently using:

=LEFT(A1,3)

However, this will not pull correctly if the postal code begins with 0.

Example: 04587 will pull 458 instead of 045.

Any suggestions?
 
Pcakes said:
I have to extract the first 3-digits of a postal code, currently using:

=LEFT(A1,3)

However, this will not pull correctly if the postal code begins with 0.

Example: 04587 will pull 458 instead of 045.

Any suggestions?
Click to expand...

If you insert your codes as text, then your formula will work OK.

If your cell actually contains the number 4587, rather than the code 04587,
you might try
=IF(LEN(A1)=4,0&LEFT(A1,2),LEFT(A1,3))

If you have other formats than the 5 digits, expand the expression to suit.
 
Hi Pcakes,

I didn't understand your question if it is a text the formula works

the postal code data are formated as a text or value?


regards
Marcelo

"Pcakes" escreveu:
 
Hi

It sounds as though the input of the code in the first instance needs to
have included a single quote in front to force it to be text '04587

You could use =LEFT("0"&A1,3)
 
They are custom formatted, as zipcode.

Marcelo said:
Hi Pcakes,

I didn't understand your question if it is a text the formula works

the postal code data are formated as a text or value?


regards
Marcelo

"Pcakes" escreveu:
Click to expand...
 
Hi
Thanks for the feedback, but I think David Biddulph gave a more useful
and generalised solution.
 
Thank you most helpful!

David Biddulph said:
If you insert your codes as text, then your formula will work OK.

If your cell actually contains the number 4587, rather than the code 04587,
you might try
=IF(LEN(A1)=4,0&LEFT(A1,2),LEFT(A1,3))

If you have other formats than the 5 digits, expand the expression to suit.
Click to expand...
 
I have to extract the first 3-digits of a postal code, currently using:

=LEFT(A1,3)

However, this will not pull correctly if the postal code begins with 0.

Example: 04587 will pull 458 instead of 045.

Any suggestions?
Click to expand...

=LEFT(TEXT(A1,"00000"),3)


--ron
 
Back
Top