1

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
  • 1
    What did you try?
    – Rubén
    Dec 29, 2017 at 15:47

3 Answers 3

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.

0

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

0

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 agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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