Find the index of substring in whole string in one cell

Hi Guys,

it is cross posted here for formula solution:

https://www.excelforum.com/excel-for...ml#post5583939

In this topic i am looking for VBA solution - honestly formula from abovve topic is a magic for me, VBA i know better so it would be nice to compare solutions.

i have joined string like here:

"Vn_Input_HANA_Deployment_Description;Vn_Input_NetWeaver_Deployment_Description;Vn_Input_Oracle_Deployment_Description;Vn_Input_SBD_Deployment_Description;Vn_Input_Servers_Deployment_Description;Vn_Input_SharedStorage_Deployment_Description;Vn_Input_WebDispatcher_Deployment_Description"

or here : "Vn_Input;Vn_Input;Vn_Input;Vn_Input;Vn_Input;Vn_Input"

and now i have to lookup and find indexes for all matches in source table.

So if i am looking for Vn_Input_HANA_Deployment_Description i would like to get "1" because it is the first substring in whole string.

in second example where lookup value is: "Vn_Input" i would like to get 1;2;3;4;5;6 because i have everywhere matches.

IT is possible with formula ? OR maybe VBA would better (if Yes admin please move to proper forum topics).

I am attaching example workook to address.

And in my source table i have also TableName to retrive.

So generally steps i would take:
1) find the row of source table where lookupvalue exists (we will not have duplicates here),
i tried with :
Copy to clipboard
"=INDEX(Source!E2:E6;MATCH(LookUp!C4;Source!$E$2:$E$6;0))"
but it is not working
2) within found row check which position in string lookup value has and return index.
3) if more than one matches return array as result.

Thank you for help,
Jacek

Can anybody help with VBA solution?
Best Wishes,
Jacek

Please Login or Register  to view this content.

Thank you jindon,

perfect solution!

Best wishes,
Jacek

Hi jindon,

Please Login or Register  to view this content.

i do not catching what it is doing.
It is setting a(i,2) AS arraylist right ?

so a(i,2) is the arraylist now? Not array?

It is like setting dictionary?

Best,
Jacek

Setting each second column of array to ArrayList for corresponding sub string of 1st column to get unique index and sort later.

If this is easier to understand.

Please Login or Register  to view this content.

thank you jindon!

Jacek