Paste a Docs numbered list into Sheets and modify the data using a formula

Question

I have a numbered list in Google Docs. Each list item contains a URL, one of three emojis, and a name.

Docs List Example

1. https://news.ycombinator.com/item?id=38122061 🥾 D. onohoe
2. https://news.ycombinator.com/item?id=38076530 🦈 H. Trp
3. https://news.ycombinator.com/item?id=33411748 🐟 Drew P.
4. https://news.ycombinator.com/item?id=29083367 🥾 J. Abo
5. https://news.ycombinator.com/item?id=15614660 🐟 Ben Breen

List-to-Table (Docs-to-Sheets)

I want to copy the numbered list into Sheets such that:

1. Each list item is on a separate row.
2. Each list item is split so that the URL, emoji, and name, are each in their own column.
3. I want the emojis swapped for an associated status (text string).

Desired Result

#	URL	Name
1	https://news.ycombinator.com/item?id=38122061	In Progress	D. Onohoe
2	https://news.ycombinator.com/item?id=38076530	Finished	H. Trp
3	https://news.ycombinator.com/item?id=33411748	Reviewed	Drew P.
4	https://news.ycombinator.com/item?id=29083367	Pending	J. Abo
5	https://news.ycombinator.com/item?id=15614660	Submitted	Ben Breen

Highlighting and selecting a table does not work and pasting the information into a table only fills the first cell. For processing and then eventual migration or referencing into Google Sheets.

Answer 1

TLDR

=LET(vals,A2:A, lookup,G:H, emoj,INDEX(lookup,,1), {"URL", "Status", "Name"; BYROW(TOCOL(vals, 1), LAMBDA(v, LET(e,REGEXEXTRACT(v, TEXTJOIN("|", 1, emoj)), SPLIT(REGEXREPLACE(v, "\s*" & e & "\s*", e & VLOOKUP(e, lookup, 2, 0) & e), e))))})

BYROW  INDEX  LAMBDA  LET  REGEXEXTRACT  REGEXREPLACE  SPLIT  TEXTJOIN  TOCOL  VLOOKUP

   Instructions

1. Copy your ordered list from Docs
   
    
2. Paste it into Sheets in A2, and each list item will be placed in a separate row
   
    
3. Place your emoji-to-status mappings in Columns G to H as shown in the table 'Sample data > Markdown ' at the bottom of the page.
   
    
4. Place this array formula in cell C1^{*
   * Columns C to E should be empty except for the formula}

   =LET(vals,A2:A, lookup,G:H, emoj,INDEX(lookup,,1),
     {"URL", "Status", "Name";
      BYROW(TOCOL(vals, 1), LAMBDA(v,
        LET(
          e,REGEXEXTRACT(v, TEXTJOIN("|", 1, emoj)),
          SPLIT(REGEXREPLACE(v, "\s*" & e & "\s*",
            e & VLOOKUP(e, lookup, 2, 0) & e), e))))})

  Explanation

1. vals holds the pasted values from Docs
2. lookup contains the emoji-to-status mappings
3. emoj is the emoji column from lookup
4. BYROW passes each value from vals, one by one, into a LAMBDA function that stores the current value in v. TOCOL was used to remove any blank rows.
5. The LAMBDA''s formula uses REGEXEXTRACT to store in e the emoji found in the current value
6. REGEXREPLACE replaces the emoji e including any extra spaces by its status message sandwiched between two e emojis. The status is returned using VLOOKUP.
   For example: If e="🥾" then "🥾Finished🥾" will replace "🥾" or "  🥾" or "🥾   " or " 🥾 " etc.
7. SPLIT then separates the string into three columns using the emoji e as its delimiter argument.
   For example, if:

   v = "https://news.ycomb... 🥾 D. onohoe"
   

   Then:

   =SPLIT(REGEXREPLACE(v, "\s*" & e & "\s*", e & VLOOKUP(e, lookup, 2, 0) & e), e)
   =SPLIT(REGEXREPLACE(v, "\s*🥾\s*", "🥾Finished🥾"), "🥾")
   =SPLIT("https://news.ycomb...🥾Finished🥾D. onohoe", "🥾")
   ={"https://news.ycomb...", "Finished", "D. onohoe"}
   

   Sample data

1. Markdown table:

   	A	B	C	D	E	F	G	H
   1	⬇️   Paste Values Below   ⬇️		URL	Status	Name		Emoji	‑to‑Status Mappings
   2	https://news.ycombinator.com/item?id=38122061 🥾 D. onohoe		https://news.ycombinator.com/item?id=38122061	Finished	D. onohoe		🥾	Finished
   3	https://news.ycombinator.com/item?id=38076530 🦈 H. Trp		https://news.ycombinator.com/item?id=38076530	In Progress	H. Trp		🦈	In Progress
   4	https://news.ycombinator.com/item?id=33411748 🐟 Drew P.		https://news.ycombinator.com/item?id=33411748	Reviewed	Drew P.		🐟	Reviewed
   5	https://news.ycombinator.com/item?id=29083367 🥾 J. Abo		https://news.ycombinator.com/item?id=29083367	Finished	J. Abo
   6	https://news.ycombinator.com/item?id=15614660 🐟 Ben Breen		https://news.ycombinator.com/item?id=15614660	Reviewed	Ben Breen

2. Screenshot: