formula for Canadian Postal Codes
 

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

formula for Canadian Postal Codes
 

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"LB" wrote in message
...
Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

formula for Canadian Postal Codes
 

=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

formula for Canadian Postal Codes
 

Thanks for the info. I'm not sure how to format the entire column so that
when I type b2j3e6 in one row, then m4y1k9 in another, they change.

lb
"ufo_pilot" wrote in message
...
=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

formula for Canadian Postal Codes
 

In an adjacent column............say H

=UPPER(LEFT(G1,3)&" "&RIGHT(G1,3))

Double-click on the fill handle of H1 to fill down.


Gord Dibben MS Excel MVP


On Thu, 25 Jan 2007 12:22:30 -0400, "LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

formula for Canadian Postal Codes
 

Take the formula you were given & copy it as far as you like down the
column. Note that this won't change what is in your original column, but
will give the output in a separate column where you've put your formula.
After that, if you wish to you can replace the original column by doing a
copy & paste special/ values.
--
David Biddulph

"LB" wrote in message
...
Thanks for the info. I'm not sure how to format the entire column so that
when I type b2j3e6 in one row, then m4y1k9 in another, they change.

"ufo_pilot" wrote in message
...
=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.

formula for Canadian Postal Codes
 

For new entries you could use event code to change them as you entered them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 7 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Select the sheet tab and "View Code".

Copy/paste the above into that module.

As written operates only on column G........Target.Column < 7

Existing entries in Column G can be changed by selecting each cell and F2 then
ENTER


Gord Dibben MS Excel MVP

On Thu, 25 Jan 2007 13:23:51 -0400, "LB" wrote:

Thanks for the info. I'm not sure how to format the entire column so that
when I type b2j3e6 in one row, then m4y1k9 in another, they change.

lb
"ufo_pilot" wrote in message
...
=IF(A4<"",UPPER(LEFT(A4,3)&" "&RIGHT(A4,4)),"")
this one worked for me

"LB" wrote:

Hi there. Can someone help me out with a formula to go from this:
b3k2m6

too this:
B3K 2M6

capitals included?

I have them all in one column, G.
Thannks.