we are going to talk about cleaning up the format of Canadian postal codes I worked this
week on a list of thousands of entries and the postal codes had been entered a number
of people and so you had a great variety and the formatting was quite often off.
So I developed a method of testing the formatting of the cell and then apply the correct formatting
to the postal codes so let us start go to the first one here so the first thing that
we are going to check is we are going to check and see that there is seven characters so
that there is a space in between the first three characters and the second three characters
. So let us start with an if.
= if and then we are going to go len and then we will go opening bracket and then we are
going to select the cell that we would like to select the length of and then we are going
to close the bracket and we are going to go if it is less than 7 then there is not a space
so we are going to add a space we are going to select the first 3 characters from the
left side, left and opening bracket and we are going to select the cell and we are going
to put a comma in there and then we will close the bracket and then we need to put a space
in there so let us put the and symbol and quotation marks and a space and quotation
marks and now we are going to get the three characters from the right side so let us go
the and symbol again and we will go right and we will go select the cell and we want
the characters and then we are going to close the bracket and we are going to close that
bracket we have done the space now and then if the length is 7 or greater we are just
going to put a comma there and we are going to supply the value of the cell.
select ; the cell and then we are going to close the bracket now this looks like it is
fine except there is one problem we need to have all the letters in upper case.
So lets put an upper around this so let us go in front of the if upper which is specifying
that we need to have upper case here and then we need to have a closing bracket at the end.
press enter there was not any change there but drag down
and so here we have a postal code that has no spaces and no capital letters and here
it has formatted it perfectly it has put spaces in all of these and has changed them to upper
case.So that is how we format our Canadian Postal Codes in Excel Please Subscribe