Search code examples
excelexcel-formula

Can I have an ordered list with both numbers and letters in it?

So I have a cell that contains three lists with line breaks separating them:

How the data is currently set up

Is there a way to make it so there is both a numbered and alphabetical list? E.g.

  1. a. First Point b. Second Point c. Third Point
  2. a. First Point b. Second Point c. Third Point
  3. a. First Point b. Second Point c. Third Point

I've tried asking ChatGPT but it can never give me the answer I want.

I've tried using formulas given to me by ChatGPT such as:

=TEXTJOIN(" ", IF(ISNUMBER(SEARCH("a", I3)), ROW(), ""), "a") & ". " & SUBSTITUTE(I3, "a", "a", 1)

But that resulted in the following result:

The outcome of the above formula

Solution

  • Would this be sufficient?

    enter image description here

    Formula in B1:

    =LET(s,TEXTSPLIT(A1,,CHAR(10)),TEXTJOIN(CHAR(10),,SEQUENCE(ROWS(s))&". "&s))

    As per comments, an edited version:

    enter image description here

    =LET(s,TEXTSPLIT(REDUCE(A1,27-SEQUENCE(26),LAMBDA(x,y,SUBSTITUTE(x,y,CHAR(y+96)))),,CHAR(10)),TEXTJOIN(CHAR(10),,SEQUENCE(ROWS(s))&". "&s))