0

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.

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

example of substitution done by formula

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.

1

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

sample output

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.

1

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

sample output

Then just autofill for as many cells as you need.

  • 1
    This would work if it were one line per cell, but I think the question is asking about performing this modification for multiple lines within one cell. – Christopher Su May 24 '17 at 20:00
  • @ChristopherSu: If that's the case, then the Asker is really using the wrong tools for the job. – ale May 24 '17 at 20:02
  • Agreed, still a fun formula-writing exercise though :) – Christopher Su May 24 '17 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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