Hi guys,
I was wondering if anyone knows a way to seach in excel for unique charachters.
For example, I would like to look for a 2 upper case letters ( for instance: "NY")
Is there any way to to mimic the regex [A-Z] function?
Thanks.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel substitute for Regex
0
Discussion
Please don't forget to select the answer that worked for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1989) Jun 5, '20 at 3:23 am
Answers
0
Selected Answer
Hi there! I was actually thinking about this and put together a formula that might do the trick. It's not regex but it basically does what you are asking.
Let me know how this works:
=IFERROR(AND(LEN(A1)=2,FIND(LEFT(A1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,FIND(RIGHT(A1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0),FALSE)Cell to check is A1 in this example.
Discussion
Hi,
Loved your soution. It is indeed capable to find these charchters.
I though maybe we can replace the Left/Right functions with Mid.
"FIND(MID($A$1,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0"
This way it can be more dynamic if I'd like to look up for more charachters (for example NYC). but beside that, it is an itersting solution.
Thanks.
IlanLo (rep: 2) Jun 5, '20 at 6:50 am