Format for Canadian Postal Codes

this one cannot be achieved with any custom formatting, meaning, you would need to use a helper column for splitting the string into two pieces. another approach would be a VBA macro, which would do the splitting in the background and in which case you would not need a helper column.

method with helper column, put the formula in B1:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

why don't you share an excel spreadsheet with sample data and i will set it up for you?

here is the VBA macro method, in case you are inclined towards an automated solution:

Please Login or Register  to view this content.

i had forgotten to attach a sample file - here it is now.

in column A, type any postal code in any_case and hit enter > see it automatically change to UPPER_CASE with a space inserted between 3rd and 4th characters.

Hi icestationzbra,
Yes, I will share the sample data.

Thank You very much.

@rmcc, see my post #5 - i have uploaded a sample file in it with the code embedded. enable macros, follow the directions in that post and test with your sample data directly in that file.

here you go...

enable macros and play with it.

i tested the file that i had uploaded, and it works like a charm.

when you open the file, do you see an option that i have highlighted in the attached picture below?

rmcc_847043_23jul12_03.png

once you click on the Options button, it should pop-up another window like the one below:

rmcc_847043_23jul12_04.png

on this one, you have to select Enable Content and click on OK for the macro to work (so, you do not actually RUN the macro; it gets enabled and activated and runs in the background).

have you done all these?

i am using Excel 2007 too. what Operating System are you on?

---------- Post added at 07:50 PM ---------- Previous post was at 07:46 PM ----------

by the way, i have hit a bug on that file that i had uploaded earlier. i am going to open a thread in the VBA section on this issue.

the code works fine for all columns except for the column in which you have the Data Validation and it flips out completely. so, just wait for a bit.

rmcc, you should save it as a Macro Enabled file for Macros to work...

only when you do that will you get the prompt for enabling macros.

the solution is macro-based, you will need to save the file as a macro-enabled file and enable macros for the solution to work for you.

i have created another thread for the issue with Data Validation:

http://www.excelforum.com/excel-prog...ing-macro.html

rmcc,

see the attached file - the macro in it was provided by Jerry Beaucaire on another thread.

download this file as a MACRO ENABLED file. enable macros and then test it out. only text entered in columns A, B, C, D, E and G will be converted to Proper_case by this macro. column H should be handled through Custom Format, which you already have setup.

rmcc, you mentioned that you downloaded the file, but it is not working.

take me step by step through what you did to download the file - and, first of all, did you download the file from here in this thread?

cool - first, and major, step conquered...

if you feel comfortable in sharing your work file with me (devoid of data, of course), i can setup the macro in it for you.

if you want to try it yourself, here is how to do it.

open your work file. determine which is the sheet that has the table for names, address etc. let us say it is Sheet1. now, right-click on the sheet tab where it says Sheet1. one of the options should be View Code, select that. once you select that option, a new window, know as the VB Editor in general parlance, should pop up. that window should look like the following image:

rmcc_847043_24jul12_06.png

the left-side pane should have Sheet1 highlighted and selected (that is the sheet tab you right-clicked on). make very sure there is nothing on the right-side pane. if there is something in there, that is probably code you need, too. so revert here in that case.

now, switch over to this forum thread.

take the following code (highlight all the way from Option Explicit to End Sub and copy):

Please Login or Register  to view this content.

after you have copied the code above, paste it within the right-side pane, just below where it says General and Declarations. now, your window should look something like this image below:

rmcc_847043_24jul12_07.png

that's it - click on SAVE, close the window, switch back to your spreadsheet and start plonking data in.

remember, you have to enable macros EVERY TIME you open the file.

I really want to learn how to make the macro work, but still having problems; probably gave you the wrong columns.

Attach is the work file, but I still want to learn how; where do I put in the code which columns Excel should format?

Thanks a lot for your help.

psst see the button top left of code click that then copy

rmcc,

here you go - as soon as you download this file, let me know and i will delete it from my post. you should also delete your file from your post #21.

EDIT: file deleted.

rmcc,

moderator's have removed it, so no worries, hopefully.

if your requirement is satisfied, please close the thread as SOLVED.

rmcc,

moderator's have removed it, so no worries, hopefully.