How to generate a basic HTML (<p> and <br>) version of another cell in Google Sheets?

Question

I'm using Google Sheets to help prepare posts for various social media sites. For some of the sites, I need to cut-and-paste HTML because my posts contain YouTube videos, and the only way to avoid using the site's UI is to post HTML directly.

So, in my sheet, I have a column main text with content such as this:

Paragraph 1

(empty line)

Paragraph 2

(empty line)

Paragraph 3

Now I want to make another column (basic html) that references main text:

<p>Paragraph 1</p>
<br>
<p>Paragraph 2</p>
<br>
<p>Paragraph 3</p>

Here is how my data is formatted:

• There is always at least one paragraph. There may be as many as five paragraphs.
• There is always an empty line between paragraphs.

So this is what I want to do:

• If a line contains text, add a <p> tag in front and a </p> tag in back.
• If there is an empty line, convert it into a <br> tag.

I have read through the list of Google Sheets functions and I have some basic familiarity with the Script Editor, but I don't know what direction to take to try to implement this.

Answer 1

It's not a pretty formula, but I got this to work (replace A1 with cell that has the source text to perform the modifications on):

=SUBSTITUTE(CONCATENATE("<p>", SUBSTITUTE(SUBSTITUTE(A1, "

", "
<br>
"), "
", "</p>
<p>"), "</p>"), "<p><br></p>", "<br>")

Note that the line breaks in the formula are important. This also assumes that the first line of the cell is not an empty line.

Answer 2

Here's a solution which will work if all the text is in a single cell, and doesn't require funky line breaks in the formula.

=concatenate("<p>",substitute(A1,CHAR(10)&CHAR(10),concatenate("</p>",CHAR(10),"<br>",CHAR(10),"<p>")),"</p>")

This assumes, however, that the paragraphs are separated with a regular line feed character (U+000A), which you can generally get in Google Sheets by using Ctrl+Enter.

Answer 3

I haven't verified this:

=IF(ISBLANK(A1),"<br>",CONCATENATE("<p>",A1,"</p>"))

In other words...

if the cell A1 is blank, then return a "<br>", else, return the contents of A1 concatenated with a prepended "<p>" and appended "</p>".

Then just autofill for as many cells as you need.