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.

  • 1
    What did you try? – Rubén Dec 29 '17 at 15:47
up vote 1 down vote accepted

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.

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

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&", "))

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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