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

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?

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

"Pcakes" wrote in message
...
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.
--
David Biddulph

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

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:

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?

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

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)

--
Regards

Roger Govier


"Pcakes" wrote in message
...
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?

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

They are custom formatted, as zipcode.

"Marcelo" wrote:

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:

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?

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

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

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Pcakes" wrote in message
...
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?

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

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

--
Regards

Roger Govier


"Pcakes" wrote in message
...
I guess I am simply not thinking correctly! What a simple fix, thanks!

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Pcakes" wrote in message
...
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?

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

Thank you most helpful!

"David Biddulph" wrote:

"Pcakes" wrote in message
...
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.
--
David Biddulph

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

On Thu, 22 Jun 2006 06:44:02 -0700, Pcakes
wrote:

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