Regex exact match function

**jaryszek** · 05-01-2019, 11:39 AM

Hi,

i am using this code which is failing:

Please Login or Register  to view this content.

Runtime Error 5020...

Why?

How to find exact match within string above using regex?

Please help,
Jacek

**rorya** · 05-01-2019, 11:42 AM

Why use RegEx for that? Just use Instr or Like.

**jaryszek** · 05-01-2019, 11:45 AM

hi Rory,

thank you.
Because i do not know how to use instr with it.

in textString i have 2 indirects formulas so i want to skip this.
How can i write a statement for this?

What i have is Table Name from dictionary.

Jacek

**rorya** · 05-01-2019, 11:49 AM

Please Login or Register  to view this content.

would be True if the text is found.

**jaryszek** · 05-01-2019, 11:55 AM

ok thank you.

but in this case it will work every time i have indirect formula there.
I want to catch when i have at least one table name without indirect.

What if i have:

textString = "=INDEX(T_DATACENTERSSOURCE[DATACENTER]),MATCH([@RegionName],INDIRECT(""T_DATACENTERSSOURCE[LOCATIONID]""),0))"

so one is indirect, one is not indirect and i have to have TRUE here and go further (not skip).

Best,
Jacek

**rorya** · 05-01-2019, 12:06 PM

That's not what your original code would have done. You can use Like and test for the table name without a quotation mark in front of it:

Please Login or Register  to view this content.

**jaryszek** · 05-01-2019, 12:16 PM

thank you Rory!

what is this in your code?:

"["

Jacek

**rorya** · 05-01-2019, 12:20 PM

It's a character group, much like in RegEx.

**jaryszek** · 05-01-2019, 12:39 PM

very nice,

thank you!

this is so many exceptions here.
i need regex for this i think.

for example i have "T_ser" and looking within "T_serdetails".

in this formula:

textstring Like "*[!""]T_serdetails*" it will match but it shouldn't because i have different exact matched string...

Please help,
Jacek

**jaryszek** · 05-02-2019, 01:08 AM

Hi,

anyone?

Jacek

**karedog** · 05-02-2019, 05:34 AM

To answer your question on Post #1 : Why Rutime Error ?

Because :

Please Login or Register  to view this content.

the opening bracket is part of Regex's "Special Characters" used for grouping, so if you mean the actual opening bracket sign, you must escape it like this :

Please Login or Register  to view this content.

**jaryszek** · 05-02-2019, 05:51 AM

hi,

thank you.

Ok this is still not working for me.

the goal is:

if i have at least one indirect within formula - skip the formula. (set function to false).

Rory provided solution how to check if indirect is there:
If we have table name with quotation mark - skip because this is indirect.

i tried use this code:

Please Login or Register  to view this content.

but pattern is not working. I can not delete the last quotation mark:

Screenshot_11.png

Please help with this,
Jacek

**KOKOSEK** · 05-02-2019, 05:58 AM

Why still not with InStr:

Please Login or Register  to view this content.

**jaryszek** · 05-02-2019, 06:01 AM

thank you KOKOSEK,

because if i will have table name like "T_DATACENTERS" it should not find it because it would be not exact match.
Instr will find evenr part of the string.

Jacek

**rorya** · 05-02-2019, 06:11 AM

You're missing a quote:

Please Login or Register  to view this content.

**jindon** · 05-02-2019, 06:13 AM

StrComp Function

https://docs.microsoft.com/en-us/off...rcomp-function

**karedog** · 05-02-2019, 06:29 AM

Still confused, by your description, try this :

Please Login or Register  to view this content.

**jaryszek** · 05-02-2019, 06:33 AM

thank you Rory! (sorry i can not add reputation to you i am adding it too often).
Working like a charm.

Ok and if i want to find out if there is exact table name but WITHOUT preceding quotation mark (so at least one no indirect table reference in formula), how the pattern will be look like?

i tried with:

Please Login or Register  to view this content.

but this is throwing error.

Please help,
Jacek

**jaryszek** · 05-02-2019, 06:37 AM

karedog, thank you!

Can you please explain this pattern?

Please Login or Register  to view this content.

\W - special character
what is "\" doing ?

Jacek

**jaryszek** · 05-02-2019, 06:41 AM

jindon, thanks!

how would you use this strcomp function together with instr?

It would be nice solution somehow?

Jacek

**jindon** · 05-02-2019, 06:44 AM

Use it instead of Instr like

Please Login or Register  to view this content.

**karedog** · 05-02-2019, 06:48 AM

You are welcome, thanks for reps.

You want an exact match, so :

INDIRECT(""T_DATACENTERSSOURCE[DATACENTER]
should be matched

and

INDIRECT(""T_DATACENTERSSOURCExxx[DATACENTER]
should not be matched

To distinguish between them, in your case, there is direct "opening square bracket character [" immediately after the table name, and it is part of Regex's \W range.

**jaryszek** · 05-02-2019, 06:53 AM

karedog,

thank you.

jindon, thank you,
but this is not wotking

i tried with:

Please Login or Register  to view this content.

and with :
PatternToFind = """" & tablename

but still is not = 0...

Jacek

**jindon** · 05-02-2019, 07:02 AM

I didn't read the problem carefully,
Perhaps [ at the end of variable like

Please Login or Register  to view this content.

Then InStr Function ?

**jaryszek** · 05-02-2019, 07:05 AM

thanks, the solution was provided by Rory with regex function.

All other methos will work but i have a lot of cases so regex working the best.

Question is one:

Please Login or Register  to view this content.

How to:

Ok and if i want to find out if there is exact table name but WITHOUT preceding quotation mark (so at least one no indirect table reference in formula), how the pattern will be look like?

i tried with:

Please Login or Register  to view this content.

but i failed.

Jacek

**jaryszek** · 05-02-2019, 08:32 AM

This is very difficult.

I tried also with this one link:

https://www.regextester.com/15

Screenshot_12.png

as you can see i didn't find a word ""badword" here so it is working fine.

but when i moved it to pattern:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

this is giving false witgin Regex function...

Please help,
Jacek

**rorya** · 05-02-2019, 09:19 AM

Remove the two / characters.

**jaryszek** · 05-02-2019, 10:47 AM

thank you Rory,

not working

Please Login or Register  to view this content.

it should give me a TRUE here...

Jacek

**rorya** · 05-02-2019, 10:49 AM

That / should be \

**jaryszek** · 05-02-2019, 10:52 AM

Still i am getting FALSE :/

Please Login or Register  to view this content.

**jaryszek** · 05-03-2019, 01:12 AM

After spending a lot of hours on it i have!!!

Please Login or Register  to view this content.

thank you!

Jacek

**jaryszek** · 05-03-2019, 01:31 AM

Ok this is still not working

this is not looking for exact match!

Please Login or Register  to view this content.

this should be false because i have within lookup string T_DATACENTERSSOURCE , not T_DATACENTERS...

Please help,
Jacek

**jaryszek** · 05-03-2019, 02:40 AM

problem solved:

Please Login or Register  to view this content.

Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Re: Regex exact match function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Found function with exact match

[SOLVED] Exact text match within a SUMIF function

[SOLVED] Exact Value match in Lookup function

Bookmarks

Bookmarks

Posting Permissions