1

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.

s_b
  • 27
  • 2

2 Answers2

0

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.

Queeg
  • 7,748
  • 1
  • 16
  • 42
  • The order is given by the string. b is the last one so it gets 0. – s_b Oct 14 '22 at 21:25
  • Then simply count the number of semicolons after your string component. B has zero semicolons coming afterwards, but A has three of them. – Queeg Oct 14 '22 at 21:30
  • This is what I want, can you provide a simple formula for that without using vba stuff? – s_b Oct 15 '22 at 08:49
  • Got it, it's in german '=LET(endString; RECHTS(A$1;LÄNGE(A$1)-SUCHEN(C2;A$1)); LÄNGE(endString)-LÄNGE(WECHSELN(endString;";";"")))' – s_b Oct 15 '22 at 09:22
0

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: sample excel file

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.

Notes

If you don't have TEXTSPLIT available in your excel version, you can try instead:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,";","</s><s>")&"</s></t>","//s")

Remember FILTERXML has also some constraints: it is not available in Excel for the web and Excel for Mac. Since the solution generates column-wise arrays, you need to transpose the output of FILTERXML, i.e. TRANSPOSE(FILTERXML(...)).

An alternative if you can't use FILTERXML is the following one. Adapted the response of the question: Split a string (cell) in Excel without VBA (e.g. for array formula) provided by: @Carble:

=LET(txt,A1,del, ";",length, LEN(txt)+1, 
items, length-LEN(SUBSTITUTE(txt,";","")),
seq, SEQUENCE(items,,0), findSpaces, FIND(" ",SUBSTITUTE(txt,del," ",seq)),
startTxt, IFERROR(findSpaces+1,1),startDel1, IFERROR(findSpaces,0),
startDel2, IFERROR(FIND(" ",SUBSTITUTE(txt,del," ",seq+1)), length),
MID(txt,startTxt,startDel2-startDel1-1)
)

If your version doesn't allow to use LET, then just replace the value of the variable defined in the formula. It will require SEQUENCE (available from O365 2021). Not having even SEQUENCE will make it very hard. A helper columns will be required to generate the sequences.

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Looks promising, thank you! But Textsplit is available only in the beta yet. I have an education licence so I guess I can't upgrade it -.- – s_b Oct 15 '22 at 08:47
  • Next time you need to specify in the question your version constraints (there are specific excel version tags), so a specific solution can be provided. You can try `FILTERXML` but it doesn’t work for Excel for the Web, which is the version I have, so I cannot test it. Check [here](https://www.howtoexcel.org/split-text-by-delimiter/) how. – David Leal Oct 15 '22 at 18:04
  • @s_b Updated the question to consider other Excel version scenarios. I hope it helps. – David Leal Oct 15 '22 at 21:23