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

[Pcakes]

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?

 

[David Biddulph]

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?

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.

 

[Marcelo]

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:

 

[Roger Govier]

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)

 

[Pcakes]

They are custom formatted, as zipcode.

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:

 

[Pcakes]

I guess I am simply not thinking correctly! What a simple fix, thanks!

 

[Roger Govier]

Hi
Thanks for the feedback, but I think David Biddulph gave a more useful
and generalised solution.

 

[Pcakes]

Thank you most helpful!

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.

 

[Ron Rosenfeld]

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?

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


--ron