Find the index of substring in whole string in one cell

Hi Guys,

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 :

Please Login or Register  to view this content.

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

EDIT: Link to VBA solution topic:
https://www.excelforum.com/excel-pro...ml#post5584286

You might want to add in the expected results?

Yes I think it would help to show what results you expect and where.

Assuming I understand what is needed, here's how I see this happening. I'm not sure which programming language I would choose, so I will also include options:

1) Start with a semi-colon delimited text string.
2) Split the text string into an array of elements.
2a) In Excel, Text to columns. (helper cells)
2b) In Google Sheets, Text to columns or split() function. (helper cells)
2c) In VBA, split() function
3) Search each element of the resulting array for the desired text substring.
3ab) In Excel or Google Sheets: FIND() function (helper cells).
3c) In VBA: Instr() function (inside of a loop) (store as array of integers)
4) join the resulting array of integers into a semicolon delimited text string.
4a) In Excel: TEXTJOIN() function (assuming you have access to it).
4b) In Google Sheets: TEXTJOIN() function (since they don't hide anything behind subscription walls).
4c) In VBA: Join() function.

I would not be surprised if Power Query/Get and Transform has a similar set of commands, so that you could use Power Query's programming language to do this, too.

At this point, it seems mostly a question of which programming language you prefer, which is probably as much as anything about how you want to interact with the spreadsheet.

Maybe try this with Ctrl+Shift+Enter

=MID(SUBSTITUTE(TEXT(MID(NPV(9;IFERROR(IF(FILTERXML("<x><m>"&SUBSTITUTE(INDEX(Source!$E$2:$E$6;MATCH(1;FREQUENCY(0;1/(1+LEN(SUBSTITUTE(Source!$E$2:$E$6&";";C4&";";C4&11))-LEN(Source!$E$2:$E$6)));));";";"</m><m>")&"</m></x>";"//m")=C4;ROW(A$1:A$9));""));3;9);REPT("\;0";6));";0";);2;99)

Thank you Guys,

Rory,please find in attachment expected result.

Screenshot_238.png

BoRy,

o wow what the monster formula! Thank you, i have to analyse.

Mr Shorty, thanks for tips.

Best,
Jacek

I rarely use volatile function.
No, the formula is non-volatile.

If you have Textjoin then

=TEXTJOIN(";";;IFERROR(IF(FILTERXML("<x><m>"&SUBSTITUTE(LOOKUP(0;-FIND(C4&";";Source!$E$2:$E$6&";");Source!$E$2:$E$6);";";"</m><m>")&"</m></x>";"//m")=C4;ROW(A$1:A$9);"");""))

You have a solution to this in the VBA forum. Why do you have two threads running on the same issue? If the objective is DIFFERENT, then the thread titles should also be different and reflect the difference.

Bo_Ry has not 'disappeared' - he is a volunteer here, just like all of us, and it's up to him whether or not he contributes to your threads. Please don't comment on an individual's availability - it's entirely up to them when they visit and what they do whilst here.

Yes! You know very well that it's ONE THREAD PER ISSUE here.

This thread is now closed. If you want to continue this discussion, then do so in the thread in the VBA section.