finding canadian postal codes with SEARCH function (using wildcards)

[Stevee829]

Hello there,

Is there a way to check if cells in a column contain a Canadian postal code, format X0X 0X0 (letter, digit, letter, space, digit, letter, digit) by using the SEARCH function?

Thank you!

[Logit]

VLOOKUP regardless of the format.

https://support.office.com/en-us/art...8-93a18ad188a1

[Stevee829]

Sorry, but I think you didn't get me ... :/ What I want to get this second column from the first column ("true" if a valid postal code is detected within the cell):

dajdY7U 8U9	true
dadsJ6G 4F6%?&*?	true
	false
74829	false
H6j778	false
k8v 4f9	true

[Stevee829]

My question felt into oblivion I believe...

[canapone]

Hi,

can you confirm you using Excel 2007?

Regards

[canapone]

Hi,

a brute-force attack

Please Login or Register  to view this content.

Hope it helps

[Logit]

.
.
The following seems to work ..
Paste this into a Routine Module:

Please Login or Register  to view this content.

Then, let's say all your data is in Col A ... Paste this formula in B1 and copy down:

Please Login or Register  to view this content.

This will give you either TRUE or FALSE for a match.

[XOR LX]

Try:

=ISNUMBER(MATCH(7,MMULT(0+(ABS(77.5-{0,25,0,45.5,25,0,25}-CODE(MID(MID(UPPER(A1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-6)),7),{1,2,3,4,5,6,7},1)))<{13,5,13,1,5,13,5}),{1;1;1;1;1;1;1}),0))

Regards

[Stevee829]

Hi,

can you confirm you using Excel 2007?

Regards

Sorry, it's 2010, you're right! Does it change something to the formula?

[canapone]

Hi,

No, I was considering to use AGGREGATE (Excel 2010) : same outputs

Logit's Vba code or XOR LX's formula are by far more elegant.

Regards

[Stevee829]

thanks all!

[canapone]

Hi,

thanks for providing kind feedback.

A last doubt: if your strings could contain more than one space, formula would need to be reviewed...

Regards

[Stevee829]

if your strings could contain more than one space, formula would need to be reviewed...

And if it contains no space at all, I guess it would also need to be reviewed (not for now)

[canapone]

Hi,

thanks.

I'll stay tuned in...

[Logit]

Logit's Vba code or XOR LX's formula are by far more elegant.

Thank you !

[CAABYYC]

Assume Column A is original Text content with/without Postcode and with/without space
C1=SUM(N((CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5))),1))>64)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+1,1))<65)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+2,1))>64)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+3,1))<65)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+4,1))>64)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+5,1))<65)=6))>0 then CTRL+SHT+Enter , copy down

[CAABYYC]

if you want to extract postcode (if has),
D1 =IFERROR(MID(SUBSTITUTE(A1," ",),
IF(SUM(N((CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5))),1))>64)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+1,1))<65)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+2,1))>64)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+3,1))<65)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+4,1))>64)+
(CODE(MID(SUBSTITUTE(A1," ",),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))+5,1))<65)=6))>0,MIN(ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(A1," ",))-5)))),"/"),6),"")

then CTRL+SHT+Enter , copy down