Instr function withing Regex

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

Hi,

i want to get long as first matched character from regex object.

Code is described here:

https://www.excelforum.com/excel-pro...-function.html

Please Login or Register  to view this content.

so i would like to get instr within regex so get long from first character from matched string (something like instr(1,textString ,"T_DATACENTERSSOURCE") but with exact match).
How can i do this?

Jacek

**jindon** · 05-03-2019, 01:23 AM

Need to escape meta charcter

Please Login or Register  to view this content.

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

jindon,

thank you very much.
i changed a PatternToFind (i do not need indirect here only quotation mark)

Please Login or Register  to view this content.

and this is working.

But i do not understand what you are doing jindon?

"?!(\[.*)" here you are excluding sign [ ? why? please explain this expression.

I found something like this in internet:

Please Login or Register  to view this content.

but this is returning 1 as result - i do not know why?
It should return 74 because there is a first occurrence of PatternToFind there.

Best
Jacek

**jindon** · 05-03-2019, 01:50 AM

That was my mistake.

As I mentioned in you previous thread, if you change PatternTo Find to "T_DATACENTERSSOURCE[" it will be all solved.
However you need to eacape [ by adding \ means PatternTo Find = "T_DATACENTERSSOURCE\[", so that my previous post should be ignored.

Anyway, better the add escape charcter(s) within a function.

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

jindon,

thank you. Sorry it is hard to understand. And you are saving my *** right now.
but if i will have "T_DATACENTERSSOURCE" instead of "T_DATACENTERSSOURCE[LOCATIONID]" it will not work?:

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

Best wishes for you,
Jacek

**jindon** · 05-03-2019, 02:01 AM

Meta characters

^ | $ ( ) [ ] { } + * ? . \ -

When these characters are used as a plain string in the PATTERN, it should be escaped by adding \ like

"T_DATACENTERSSOURCE\[LOCATIONID\]"

otherwise, it will be treated as a meta character as [LOCATION], means any one character from the character set.

You know what I mean?

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

yes,

thank you very much. But [LocationID] i will skip from pattern, it will be only table name. I will have only "T_DATACENTERSSOURCE".

Please Login or Register  to view this content.

Without any special/meta characters but One special character is quotation mark in front of string.

1. So i am trying to find:

Screenshot_13.png

i do not know why also "," is taken ? i do not want to have this included.
And this is not exact match.

2. If i will change it to exact table name like "T_DATACENTERS" it will also work, and it shouldn't.

Screenshot_14.png

Please help,
Jacek

**jindon** · 05-03-2019, 02:18 AM

Do you mean

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

You just need to double.

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

Ok thank you very much!

This is not what i meant but this is perhaps solving the problem, can you please confirm?

Please Login or Register  to view this content.

it will find exact match of string which is not preceded by quotation mark, it is true?

It seems that this is working.

the only problem here is that i am getting "," included. Why?

Jacek

**jindon** · 05-03-2019, 02:36 AM

Ahh, you don't want the quotation mark ahead, I thought other way.

Yeah, it should match to ",T_DATACENTERSSOURCE" part.

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

thank you jindon!

i can not add you reputatin unfortunately because i added to much.

Ok, i do not want to have ",T_DATACENTERSSOURCE", only "T_DATACENTERSSOURCE". how to do this?

Jacek

**jindon** · 05-03-2019, 02:43 AM

Only the way you can do it to add () for back reference and get it by submatches, like

Please Login or Register  to view this content.

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

Thank you

or in this function can i use???

Please Login or Register  to view this content.

so +2 will give me the exact start number of string?

Jacek

**jindon** · 05-03-2019, 02:55 AM

Should be OK.

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

thank you!!

Jacek

**rorya** · 05-03-2019, 03:45 AM

Why would you have MATCH([@RegionName],T_DATACENTERSSOURCE) when MATCH requires a single row or column, not a table?

**jaryszek** · 05-03-2019, 04:21 AM

thank you Rory,

this was just an example.

Jacek

Instr function withing Regex

LinkBack

Thread Tools

Rate This Thread

Display

Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Re: Instr function withing Regex

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Instr with RegEx - Runtime error 5017 (RegEx Code provided)

[SOLVED] help with a function using Regex

Passing a RegEx to a function

Can I place a function call withing SQL Statements?

[SOLVED] InStr Function

Which function to check any value existed withing a list?

Which function to check any value existed withing a list?

Bookmarks

Bookmarks

Posting Permissions