Google Sheets - REGEX Challenge

Question

I have a long Google Sheet column of cells with content looking like this:

#text: Jacob Zuma
@domain: https://www.theguardian.com/world/zuma

#text: World news
@domain: https://www.theguardian.com/world/world

#text: ANC (African National Congress)
@domain: https://www.theguardian.com/world/anc-african-national-congress

#text: South Africa
@domain: https://www.theguardian.com/world/southafrica

#text: Africa
@domain: https://www.theguardian.com/world/africa

I need it to look like this:

Jacob Zuma, World news, ANC (African National Congress), South Africa, Africa

Basically, i'm trying to find a way to get rid of all the headers (#text+@domain) and the URLs, and have only the subjects themselves, all lined-up, separated with commas.

Would appreciate help in figuring out how to do it.

Answer 1

Here is a possible solution.

First, I am assuming that your data is all in column a of the sheet and each two lines of text seperated by a blank line represent one cell. Also I am assuming that the pattern in column a repeats exactly after 5 cells.

1. Paste the following formula down column b:

   =trim(REGEXEXTRACT(A1,"(?:\s)[^\n]*"))

2. Paste the following formula down column c:

   =JOIN(", ",indirect(ADDRESS((row()-1)*5+1,2)&":"&ADDRESS((row())*5,2)))

Your list of desired texts will be in column c.

Answer 2

Assuming the strings in Column A, try

=Textjoin(", ", 1, ArrayFormula(if(len(A1:A), regexextract(A1:A, "\s(.+)\n"),)))

and see if that works?

(Change range to suit).

Answer 3

I feel regex is overkill here. Non-regex way -

• Sort the column to have all the words starting with "#text:" as a group
• Use the Find & Replace function (Ctrl+H keyboard shortcut) to search for "#text: " and replace the word with a blank

• Assuming cell range A1:A5 contains your subject keywords after the sort try this formula in the cell where you want the comma separate list

  =ArrayFormula(concatenate(A1:A5&", "))