I have a large Excell spreadsheet (27k records) that includes a column for
Postal Codes (Canadian). The provider did not type the space in between the
first and last 3 characters of the postal code. Can you tell me how /if I
can quickly format the Postal Code column to include the space?

I'm unfamiliar with Canadian post codes, but if they all consist of six
characters, in another column
=LEFT(A1,3)&" "&RIGHT(A1,3)
and drag down to the end of the list.
Lose the formulas by right clicking the whole range Copy, right click
again, Paste Special check Values OK
Regards,
Alan.
"mmcstech" wrote in message
...
I have a large Excell spreadsheet (27k records) that includes a column for
Postal Codes (Canadian). The provider did not type the space in between
the
first and last 3 characters of the postal code. Can you tell me how /if I
can quickly format the Postal Code column to include the space?

Canadian postal codes are letter number letter number letter number. Which
means that Santa even gets a postal code
H0H 0H0
(Not kidding... Canada Post uses that postal code for routing letters to
Santa)

--
HTH...

Jim Thomlinson


"Alan" wrote:

I'm unfamiliar with Canadian post codes, but if they all consist of six
characters, in another column
=LEFT(A1,3)&" "&RIGHT(A1,3)
and drag down to the end of the list.
Lose the formulas by right clicking the whole range Copy, right click
again, Paste Special check Values OK
Regards,
Alan.
"mmcstech" wrote in message
...
I have a large Excell spreadsheet (27k records) that includes a column for
Postal Codes (Canadian). The provider did not type the space in between
the
first and last 3 characters of the postal code. Can you tell me how /if I
can quickly format the Postal Code column to include the space?

Jim Thomlinson wrote:
Canadian postal codes are letter number letter number letter number. Which
means that Santa even gets a postal code
H0H 0H0
(Not kidding... Canada Post uses that postal code for routing letters to
Santa)


Bloody Canadian copy cats :) They copied the ice hotel from Sweden and
now they lure US kids to send mail to Canada when everyone knows Santa
or as he is known in UK Father Christmas lives in Lapland LOL


Peo

I will make you a deal. We can share Santa but I am not attaching
international postage to my Christmas lists... ;-)
--
HTH...

Jim Thomlinson


"Peo Sjoblom" wrote:

Jim Thomlinson wrote:
Canadian postal codes are letter number letter number letter number. Which
means that Santa even gets a postal code
H0H 0H0
(Not kidding... Canada Post uses that postal code for routing letters to
Santa)


Bloody Canadian copy cats :) They copied the ice hotel from Sweden and
now they lure US kids to send mail to Canada when everyone knows Santa
or as he is known in UK Father Christmas lives in Lapland LOL


Peo

So a farmer called McDonald would be
E1E10H?
:)
Alan
"Peo Sjoblom" wrote in message
...
Jim Thomlinson wrote:
Canadian postal codes are letter number letter number letter number.
Which means that Santa even gets a postal code H0H 0H0
(Not kidding... Canada Post uses that postal code for routing letters to
Santa)


Bloody Canadian copy cats :) They copied the ice hotel from Sweden and now
they lure US kids to send mail to Canada when everyone knows Santa or as
he is known in UK Father Christmas lives in Lapland LOL


Peo

I don't think you will have too much luck wht a format since the cell
contents will be text. You can however just insert a column and ust the
following formula...

=LEFT(A1, 3) & " " & RIGHT(A1, 3)
--
HTH...

Jim Thomlinson


"mmcstech" wrote:

I have a large Excell spreadsheet (27k records) that includes a column for
Postal Codes (Canadian). The provider did not type the space in between the
first and last 3 characters of the postal code. Can you tell me how /if I
can quickly format the Postal Code column to include the space?

or use this formula

=REPLACE(A1,4,0," ")

"Jim Thomlinson" wrote:

I don't think you will have too much luck wht a format since the cell
contents will be text. You can however just insert a column and ust the
following formula...

=LEFT(A1, 3) & " " & RIGHT(A1, 3)
--
HTH...

Jim Thomlinson


"mmcstech" wrote:

I have a large Excell spreadsheet (27k records) that includes a column for
Postal Codes (Canadian). The provider did not type the space in between the
first and last 3 characters of the postal code. Can you tell me how /if I
can quickly format the Postal Code column to include the space?

Good One!
Alan.
"daddylonglegs" wrote in message
...
or use this formula

=REPLACE(A1,4,0," ")

"Jim Thomlinson" wrote:

I don't think you will have too much luck wht a format since the cell
contents will be text. You can however just insert a column and ust the
following formula...

=LEFT(A1, 3) & " " & RIGHT(A1, 3)
--
HTH...

Jim Thomlinson


"mmcstech" wrote:

I have a large Excell spreadsheet (27k records) that includes a column
for
Postal Codes (Canadian). The provider did not type the space in
between the
first and last 3 characters of the postal code. Can you tell me how
/if I
can quickly format the Postal Code column to include the space?

There is no format for Canadian Postal Codes.

Assuming codes are Column A

Insert a new column to the right of column A

In B1 enter this formula.

=LEFT(A1,3)&" "&RIGHT(A1,3)

Double-click the fill handle to copy down 27k rows.

When happy, copy and paste specialvaluesokesc.

Delete original column A


Gord Dibben MS Excel MVP

On Fri, 5 Jan 2007 15:01:01 -0800, mmcstech
wrote:

I have a large Excell spreadsheet (27k records) that includes a column for
Postal Codes (Canadian). The provider did not type the space in between the
first and last 3 characters of the postal code. Can you tell me how /if I
can quickly format the Postal Code column to include the space?

Yes, you can quickly format the Postal Code column to include the space using the following steps:

1. Select the entire column that contains the Postal Codes.
2. Right-click on the selected column and choose "Format Cells" from the drop-down menu.
3. In the "Format Cells" dialog box, select "Custom" from the Category list.
4. In the "Type" field, enter the following format: L0L 0L0 (where "L" represents a letter and "0" represents a number).
5. Click "OK" to apply the new format to the selected column.

This should add the space between the first and last 3 characters of each Postal Code in the column. If you have any issues with this process, feel free to ask for further clarification.