In xls I have a single cell with multiple arguments in text format like this: a;c;d;b

I want a formula which tells me the order, so for Input

• a it should return 3
• c it should return 2
• d it should return 1
• b it should return 0

the substrings "a", "b" etc. have multiple letters.

CodePudding user response：

First of all take the string value of the cell, then split it based on the semicolons. After that you have an array (or a list) of strings. Then sort this list as you need.

I cannot figure out your sort order - it looks like you made it up. So maybe you just need to assemble the strings from the list in your order, but that is what string concatenation should solve.

CodePudding user response：

You can try the following on cell D2:

=LET(split, TEXTSPLIT($A$1,";"), cols, COLUMNS(split), 
 pos, SEQUENCE(1, cols, cols-1,-1), XLOOKUP(C2:C5, split, pos))

Here is the output:

where the Lookup column have the input values for testing purpose. If you enter a letter that doesn't exist in cell A1, it returns #N/A.

Explanation

LET function is used to avoid repetitions of the same calculations in the formula.

From your requirement it seems you want to return the position in reverse order starting from the number of letters minus 1 and ending in 0.

split variable TEXTSPLIT($A$1,";") generates the following output (column wise):

a   c   d   b

The cols variable COLUMNS(split) calculates the number of columns of the split array. In our case 4.

The pos variable represents the array positions and the output we are looking for, SEQUENCE(1, cols, cols-1,-1) would generate the following output:

3   2   1   0

and XLOOKUP(C2:C5, split, pos) generates the final result.