Format a numbered list in google docs into a table template?

Question

I have a list with a url, an emoji (of a set of 3), and a name in a numbered list.

Taking a list like this:

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

How could I fit this into a table? Keeping the numbers as variables. Swapping emojis for status. Adding a notes column. The broken urls are reflective of the list because they can be googled for the correct url, but were added to meet the 8 url submission limit on StackExchange.

#	URL	Status	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 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

You can paste your list from Docs into Google Sheets and each list item will be treated as a single row.

Assuming you paste the list in Column A, the formula below will:

1. Extract the 8 digit IDs,
2. Build URLs from the IDs,
3. Recreate the list numbering.

=LET(rng,TOCOL(A:A,1),
   { SEQUENCE(ROWS(rng)),
     ARRAYFORMULA(
       "https://news.ycombinator.com/item?id="&
       REGEXEXTRACT(rng,"\d{8}"))} )

Formula in B1

Emojis with Meaning

If the emojis map into the status messages, you can use HLOOKUP to return it.

The lookup values can be an array included in the formula or stored in a referenced range.

HLOOKUP Formula

• Example where lookup values are an array in the formula:

=HLOOKUP(      
   REGEXEXTRACT(A1,"\d{8}.?(.?)"),
   {"🐟", "🥾", "🦈";
    "Finished", "In Progress", "Reviewed"},
   2,0)

Combining the emoji mapping HLOOKUP formula with the original formula will add a third column that displays the status:

=LET(rng,TOCOL(A:A,1),
   { SEQUENCE(ROWS(rng)),
     ARRAYFORMULA(
      { "https://news.ycombinator.com/item?id="&
           REGEXEXTRACT(rng,"\d{8}"), 
        HLOOKUP(
          REGEXEXTRACT(rng,"\d{8}.?(.?)"),
          {"🐟", "🥾", "🦈";
           "Finished", "In Progress", "Reviewed"},
          2,0)})})