How to get substring in a major string with VBA

Hi

I have a few sample strings like bellow:


1: "in-Training and Development Officer Pakistan"
2: "Training and Development Officer Pakistan-in"
3: "Training and Development Officer in-Pakistan"
4: "Training and Development Officer in Pakistan"


remember these are the samples

What I would like to get is the word "in" in the major string
If I use instr or instrRev functions they pic in that is in "training"
What my logic is (You may have your own which I would like to know also) if the character on the right other than the substring and left of that substring other than the substring is non alpha then that is my word.else not

like ain ,in is not myword as it has alpha character on the left
like inin ,in is also not my word as it also have a alpha on the right
but " in-" , here in is my word

How can I pass the main string from this logic in VBA

Help would be greatly appreciated

Best Regards
Imran Bhatti

Hope that makes sense.
Imran Bhatti

What do you mean by "get"? Do you just need to check if it is present, or do you need its position?

Just determine if it exists

In addition to Phuocam's solution above, you could also use regex

Please Login or Register  to view this content.

Hi both the legend

I am signing of from my office at the moment. Its time to leave .will test both the solutions at whome after 6 PM (my time today ) and let you know the outcome .

Thanks for the review. bye for 2 hours.

Imra bhatti

Originally Posted by Phuocam

Try:

If InStr(Replace(" " & str & " ", "-", " "), " in ") > 0 Then

Tried but not working . Am I missing something

Please Login or Register  to view this content.

Originally Posted by xlnitwit

In addition to Phuocam's solution above, you could also use regex

How can I apply this Function ContainsIn?

I don't think anything is going to work if your string is actually "TraininginPakistan" since there is nothing to delimit what is the word and what isn't.

Tried this (s contains my required separated by / and - these two characters are non alpha so it should return true.but not .Here I think your function can help. I am currently not familiar with regex.

Please Login or Register  to view this content.

With my function you would simply use

Please Login or Register  to view this content.

but you will also need to change your declarations to make s a String not a Variant

Please Login or Register  to view this content.

Please Login or Register  to view this content.

ByRef argument type mismatch. Error

I guess you didn't see the edit to my post- you have declared s as a Variant and it needs to be a String.

Changed s to word in brackets

Now tested this and returns true but it is not true

TraininginPakistan"

Now see your edit

It shouldn't (and doesn't for me). What is the actual code you are using now?

Applied your edit but still problem. Do I also need to change ..Pattern = "\bin\b" to something else?

Actual code

Please Login or Register  to view this content.

in may be something else as well

That's not correct- it should be

Please Login or Register  to view this content.

not

Please Login or Register  to view this content.

Originally Posted by ImranBhatti

in may be something else as well

Might have been a good idea to start the thread with that! You could add that as a parameter

Please Login or Register  to view this content.

Thanks for the correction.Problem is 99.99 % solved . It seemed that the under score "_" has an exceptions of regex. with this expression it returns false while it is true.

There may well be a simpler solution but I think this should suffice

Please Login or Register  to view this content.

And What should I pass ContainsIn(Here?) for the new function according to my actual code

The same as before- i.e.

Please Login or Register  to view this content.

That worked for underscore as well. Thank you so much Sir Don for your precious time to review my thread and for the long long discussion.I can see the system generated the second page of the thread.

many thanks indeed for spreading the knowledge.

Best Regards
Imran Bhatti

You're welcome.