Extracting up to 3 substrings from a string

Hi, I have some really messy and large dataset which I need to extract up to 3 substrings from (max) following the occurrence of specific text.

Within the string, whenever "condition=" or "condition " occurs, I need to extract the 1st, 2nd and 3rd substring (if present) appearing directly after and before the next space within the string.

I have attached a spreadsheet with an example of what I am trying to achieve.

Many thanks for any help with achieving this.

What does [=| ] suppose to do?

Please Login or Register  to view this content.

to fetch either "condition=....." or "condition ....."

No, that is either "=" or "|" or a space.

If you want "OR", it should be (=| )

Ahhh.... my bad. It shouldn't be in "group".

Thx Jindon.... I've edited my code in message #4

Hi nankw83,
Thanks for the quick reply. I've tried your code and am getting a "Run-time error '13': Type mismatch" on this row...
Range("B2").Resize(UBound(a), 3) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(2, 3, 4))
Any idea what might be the issue?

@ sintek

I'm so in awe of what you have provided here. I tried your code on my 130,000+ row data and it ran perfectly and not to mention how quickly. Thank you so much, you have saved me so much time, frustration and formula!

Also, I've noticed that in my data there are instances where the string that is extracted has double quotes around it. If it's not too much trouble, is it possible to add to this code to still extract everything between the double quotes, including any spaces if they exist, and then remove the double quotes in the result? My example is below...

condition="Exception_id12948" >> Exception_id12948
condition "Client MediumAllows_id4162" >> Client MediumAllows_id4162
condition="Year 09" >> Year 09

@darls15;

Just out of curiosity... did you try the code in message #4?

@Haluk, thanks and my apologies for not getting back sooner. Yes, I have tested it on my master data. I'm getting a 'Run-time error '6': Overflow' and it stops at 'NoA = Range("A" & Rows.Count).End(xlUp).Row'. Any idea what the cause of this could be? Thanks

@nankw83, the error only occurred in my original file, the sample file was fine so there was something I was obviously missing there. Anyways, I have just re-tested test_v2 and no error which is awesome. The double quotes are removed, however the entries that have a space only result in the first work being extracted. Is there a way to extract everything between the double quotes or is this getting a bit tricky? In the example below, only the word Client and Year were extracted.

condition "Client MediumAllows_id4162" >> Client MediumAllows_id4162
condition="Year 09" >> Year 09

In my code, you may need to change declaration type of the "Integer" type variables to "Long"

@sintek, I've just tested sintek and got a 'Run-time error '9': Subscript out of range on this line...
If Str(ii + 2) Like "*""" Then

I did a bit of a test with my original data and it seems to be failing on rows that have the double quotes and doesn't include a space as per below...
Doesn't work - Define condition "X_AdminNetworks"
Works - Define condition "X_AdminNetworks Global"

@Bo_Ry, thanks for this. I tested ThreeSub and no errors. I did however forgot to include in my example rows that don't have have the word "condition" which resulted in the entire row being extracted. For example...

force_extension("defining.original",".pname:P_2101_notify") >> (the result should be blank here)

Also, the double quotes were removed, however the entries that have a space only result in the first work being extracted. For example...

Define condition "X_AdminNetwork Global" >> X_AdminNetwork (should be X_AdminNetwork Global)

Thanks again for your help

@Jindon; Very nice pattern

@Rick Rothstein, thanks this works perfectly and yes the "free floating" quote was a typo!

@Bo_Ry, yes it does work perfectly, thank you!

@jindon, works perfectly, thanks!

Thank you all very much for your time and input. I'm very appreciative of all your efforts to help with my problem here and now have multiple working solutions. I'm very excited to be manipulating my original data to output results exactly how I needed it