I have here formula that can easily convert the weekday text like Sunday to weekday int to 1.

=MATCH(A2,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)

My question is how to convert array-text to array-int?

Let's say for example in a cell, I have value Sunday, Monday. Do we have a formula for us to get the array for example {1,2}?

CodePudding user response：

About Let's say for example in a cell, I have value Sunday, Monday. Do we have a formula for us to get the array for example {1,2}?, I guessed that from your showing formula is used, you might have wanted 7 and 1. If my understanding is correct, when your sample formula is used, how about the following modification?

Modified formula:

=ARRAYFORMULA(MATCH(TRIM(SPLIT(A1,",")),{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0))

• In this case, Sunday, Monday is put in cell "A1".

Testing:

Note:

• When the following formula is used,

    ="{"&TEXTJOIN(",",TRUE,ARRAYFORMULA(MATCH(TRIM(SPLIT(A1,",")),{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)))&"}"
  

  • When Sunday, Monday is put in the cell "A2", {7,1} is put to a cell.

CodePudding user response：

you can also try:

="{"&JOIN(",",INDEX(MATCH(SPLIT(A2,", "),TEXT(SEQUENCE(7,1,2),"DDDD"),0)))&"}"