Issue with Validating a Canadian Postal Code in excel 2016

Hello All,

I am trying to validate a Canadian postal code in excel code. I am able to validate the code for the letters and numbers, example: e3r4t5. However I am trouble in creating a formula that is short enough to add a space between the e3r and 4t5.

my code is: =(LEFT(K21)>="A")*(LEFT(K21)<="Z")*(MID(K21,2,1)>="0")*(MID(K21,2,1)<="9")*( MID(K21,3,1)>="A")*(MID(K21,3,1)<="Z")*(MID(K21,4,1)>="0")*(MID(K21,4,1)<="9 ")*(MID(K21,5,1)>="A")*(MID(K21,5,1)<="Z")*(MID(K21,6,1)>="0")*(MID(K21,6,1) <="9")*(LEN(K21)=6)

Is there anyway to shorten this code to add a space between the third and fourth character?

Thank you.

If you want to add a space between 3rd and 4th character always, go to data->text to column and choose fixed width and then drag the pointer between 3rd and 4th character and done
=concatenate(col1,col2," ")

Also, your code is not adding any space .It is simply evaluating to TRUE OR FALSE

you could use

Formula:

Please Login or Register  to view this content.

to add space between 3rd and 4th character




if this helps then click add rep button at the bottom left corner of this post!

Hi,

Assuming data in A1, you can also use this: =REPLACE(A1,4,0," ")

This will Add a space after the 3rd character of your text string.

sorry for not explaining this properly: is there any way in the data validation code to add the check for a space between the third and fourth character

Thank you

you can use conditional formatting instead select the range of cells(say A1 to A100) you want to apply
go to home tab ---> conditional formatting--->new rule--->use a formula to determine which cell top format
copy paste

Formula:

Please Login or Register  to view this content.

change bold part according to your need
then select format select colour as red
click ok

find attached file its applied in A1 to A100

if this helps click add rep button at the bottom left corner of this post

Thank you everyone for your help with solving my problem.

Thanks for your help. Quick question about your formula: Why did you use (CHAR(ROW($A$65:$A$90) in your formula. Thank you.

You are welcome and thanks for the rep

Basically, letters A to Z are expressed as Char(65) to Char(90).

Formulas in Data validation are naturally evaluated as array formula.
So CHAR(ROW($A$65:$A$90)) (i.e. CHAR(65 to 90)) will evaluate to {A,B,C,D,E...Z}. And = operator in formula is case insensitive.

If your question is resolved, please mark the thread as solved by using Thread tool found at top of your initial post.

Thank you very much.

I know I'm late to the party, here, but I took the OP's validation formula and simply added a section to the middle area to validate a space in the middle (4th position) and then adjusted the remaining positional numbers, as well as the length, accordingly.

So, the OP's validation formula, as adjusted above, becomes:

=(LEFT(K21)>="A")*(LEFT(K21)<="Z")*(MID(K21,2,1)>="0")*(MID(K21,2,1)<="9")*( MID(K21,3,1)>="A")*(MID(K21,3,1)<="Z")*MID(K21,4,1)=" "*(MID(K21,5,1)>="0")*(MID(K21,5,1)<="9 ")*(MID(K21,6,1)>="A")*(MID(K21,6,1)<="Z")*(MID(K21,7,1)>="0")*(MID(K21,7,1) <="9")*(LEN(K21)=7)

which works in my spreadsheet.

Mine actually referenced cell H2 rather than K21. I struggled a bit with the length of the formula, since, as typed above, it exceeded the maximum length for validation formulas; however, each occurrence of "K21" being replaced with my "H2" seemed to make it short enough. When I added new rows to my table to hit double digits (H10, etc.), it still worked.

I hope this helps someone else who's struggling with this same issue here at the latter end of 2023 deal with that issue!