LinkBack URL
About LinkBacks
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!
finding canadian postal codes with SEARCH function (using wildcards)
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!
VLOOKUP regardless of the format.
https://support.office.com/en-us/art...8-93a18ad188a1
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
Last edited by Stevee829; 04-13-2017 at 05:35 AM.
My question felt into oblivion I believe...
Hi,
can you confirm you using Excel 2007?
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi,
a brute-force attack
Please Login or Register to view this content.
Hope it helps
Last edited by canapone; 04-13-2017 at 10:50 AM. Reason: doubled segment in the formula: same outputs+cosmetics
.
.
The following seems to work ..
Paste this into a Routine Module:
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.Please Login or Register to view this content.
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
Sorry, it's 2010, you're right! Does it change something to the formula?Originally Posted by 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
thanks all!
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
Last edited by canapone; 04-13-2017 at 11:42 AM.
And if it contains no space at all, I guess it would also need to be reviewed(not for now)
Hi,
thanks.
I'll stay tuned in...
Thank you !Logit's Vba code or XOR LX's formula are by far more elegant.
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks