CODEWITHSUNDEEP
excelexcel-formula
Legith
Legith

Reputation: 13

Excel formula to find matching part of index

I'm working with structure like tree graph. I have items where each one has index telling where it appears in the tree. Then i get groups of items like this:
Item 1: A-1-1-2-1-1-1
Item 2: A-1-1-1-1-2
Item 3: A-1-1-2-1-1-2
Item 4: A-1-1-2-1

What i need to find is common part of index for each group.In this example a common part would be "A-1-1-".
Is it possible to find matching part of the string in another string using excel formulas? My collague said i'd need to make vba macro using recursion to find only part of matching string, but maybe there's simplier solution we didn't find.

Upvotes: 0

Views: 52

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use:

=LEFT(A1,MAX(IF(COUNTIF(A1:A4,LEFT(A1,ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1))))&"*")=COUNTA(A1:A4),ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1))))))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 2

Related Questions