How to recover second element separate by character with index equiv-CodePudding

I got a table in Excel like this:

I used index with double equiv to have only the price for column A, the price for column B, the price for column C, I did this :

=INDEX($J$1:$L$4;EQUIV($F6;$J$1:$J$4;0);EQUIV(Z$24;$J$1:$L$1;0))

But I would like to have only the value at the right of ";" but I don't know how to combine with my index and equiv to have only the value 111,1456,44455.

I have this:

CodePudding user response：

Your question is not quite clear, I am assuming you have a multiple values separated by semicolon ";" in column Price and now you want a portion of it, in this case only Right, if that is so, here is your solution:

Price

112233;50.99

223344;15.50

3344;150.5

to get the left side, use

=LEFT(C2,LEN(C2)-FIND(";",C2)-1)

here you have to subtract -1 because we don't want to include the semicolon at the end

to get the right side, use

=RIGHT(C2,LEN(C2)-FIND(";",C2))

Result:

CodePudding user response：

EQUIV() is the french name for MATCH() am I right?

If so just use a wildcard-match:

=MATCH("*;"&$F6,$J$1:$J$4,0)

Or the french equivalent:

=EQUIV("*;"&F6;$J$1:$J$4;0)