google-sheetsgoogle-sheets-formula

Get list of unique words without numbers and symbols from paragraph range in Google Sheet?


I have list of comments in Google sheet which is sourced from Google form. It is difficult for me to list down each unique words from lots of paragraph.

I have already tested this function which provided from here: How to get a list of unique unknown words from a google sheet

=ArrayFormula(unique(transpose(split(concatenate("|"&A:Z),"|"))))

However, it does not work at all. I have attached the picture which is using the formula, and does not meet my expectation.

The list of unique words:

Google Sheet Test


Solution

  • You can try this formula:

    =UNIQUE(TOCOL(SPLIT(REGEXREPLACE(JOIN(" ",A2:A6), "[\d\W]+", " ")," ")))
    

    Sample Output:

    Output

    Reference:

    Regular Expression