Hi,
I am trying to format a column on a spreadsheet so that all of the postal codes are in the same format.
I think I would do something like:
=PROPER((LEFT(A1,3),MID( ),PROPER(RIGHT(A1,3))
but i keep getting a syntax error.
I would like the postal codes to read A1A 1A1
Any thoughts on what I can do to fix it?
Thanks in advance!!
MH
OS X Mavericks (10.9.2), Numbers for iCloud,
I assume that the value in cell A1 is "A1A1A1"
If this is so then you can format the code the way to want (in another column) like this:
=left(A1,3)&" "&right(A1,3)
the "&" is called the contcatenate operator and joins strings together
I assume that the value in cell A1 is "A1A1A1"
If this is so then you can format the code the way to want (in another column) like this:
=left(A1,3)&" "&right(A1,3)
the "&" is called the contcatenate operator and joins strings together
What is missing from this solution?
Wayne Contello wrote:
What is missing from this solution?
Points?
Sorry, I think i clicked on the wrong thing--You answered my question thoroughly!
Thanks for the help!
Wayne Contello wrote:
What is missing from this solution?
This, perhaps:
=UPPER(left(A1,3)&" "&right(A1,3))
HYllom's use of PROPER in the question implies some entries may not include the required capitalization. With Canadian postal codes, where a letter always either leads the string or follows a numeral, either PROPER or UPPER will ensure that all letters display as capitals. With other systems (eg. Britain) where many post codes contain two consecutive letters, PROPER will convert only the first (or the first following each numeral) to capitals, while UPPER will convert all letters.
Note that in either case, the conversion is 'display only,' the underlying text remains unchanged.
Regards,
Barry
Canadian Postal Codes in Numbers
