Remove HTML In Google Sheets Cells

Question

Trying to determine the best method to automatically remove HTML in all cells within a column in Google Sheets.

Example of cell data:

<span style="color:#0000FF">test</span>

I'd like to strip out all of the HTML and leave the plain text, automatically whenever a new row is added.

I'm not sure if using a regex in the cell as a formula or some type of script would be a better approach.

Answer 1

You could use REGEXREPLACE function:

=REGEXREPLACE(A1,"</?\S+[^<>]*>","")

This formula also can be converted into ArrayFormula:

=ARRAYFORMULA(REGEXREPLACE(offset(A1,,,COUNTA(A:A)),"</?\S+[^<>]*>",""))

This formula would populate down automatically. COUNTA(A:A) is number of rows in column [A] with your data

Answer 2

You can just use a Find and Replace dialog.

Find what: </?\S+[^<>]*>

Replace with: <empty string>

Search: Specific range = '<SHEET_NAME>'!<COLUMN>:<COLUMN> (e.g. 'Copy of Sheet1'!A:A)

Make sure Search using regular expressions is checked. Then, click Replace all.

Here is a screen: