1

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.

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.

1
  • Your question isn't clear. What do you mean numbers as variables? What is a variable in Google Docs? Is this a Docs to Docs question or a Docs to Sheets? Are you basically asking how to copy the list of links into rows of a table?
    – Blindspots
    Nov 3, 2023 at 15:00

1 Answer 1

0

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)})}) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.