Excel / CSV Merge Text and Cell Data for Wordpress Import

Question

I have several Wordpress HTML pages for import through CSV/excel. One of the fields is content for the Wordpress page. Since these pages are all the same except for in 3 places (2 names, 1 IMG URL) I'm trying to be efficient and upload an excel with custom fields.

What I'd like to do is merge the IMG urls and Product Names into the appropriate spot in the Excel cell text so it's imported as a complete page. I'm trying to avoid all the cutting and pasting when adding 100's of similar pages with only a few different spots.

Any tips or advice on where I can accomplish this? I haven't been able to figure it out or find help online.

Cell Data Example:

<div id="productimage" style="float:left;width:380px;">
<img alt="alternate" src="imagesource" />
</div>


<div id="productspecs"  style="float:left;padding-left:25px;">
<h2><strong>Product Name</strong></h2>
</div>

"Product Name", "alternate", and "imagesource" I have fields for in a spreadsheet .. I just don't know how to merge them into this Cell Data Example to auto-populate these new pages.

Thanks!

Answer 1

If I understand your question correctly, you have html in an Excel cell and you want to make parts of that html dynamic by referencing content in other cells of the workbook.

I assume that in your example you want to make the imagesource and the Product Name dynamic.

You can copy and paste the html into the Excel formula editor. You can increase its height, so you see more than one line at a time. The formula editor can handle line breaks.

If you want to build a string that contains double quotes, you will need to use two double quotes if the quote is inside the string and three double quotes in a row if it is at the beginning or end of a string. You can use the ampersand to concatenate strings and cell references.

With your specific example above, the formula in Excel would read somewhere along these lines (replace Sheet2!A2 etc. with the cell that holds your data. Arrange that data in a table with a row for each product, then you can copy this formula down to get the desired result.

="<div id=""productimage"" style=""float:left;width:380px;"">
<img alt=""alternate"" src="""&Sheet2!A2&""" />
</div>


<div id=""productspecs""  style=""float:left;padding-left:25px;"">
<h2><strong>"&Sheet2!B2&"</strong></h2>
</div>"

Turn on "Wrap Text" in the cell format, otherwise you will see it all in one line of code. The screenshot below uses two rows of data with different texts for image source and product name in sheet 2.

EDIT: I tried to post this in a comment, but the double and triple quotes don't make it and get replaced with just one quote.

Also, you managed to delete some of the & signs that concatenate the different strings. Please look again at the original formula I've posted. Replace the cell references with yours, but don't mangle the code. The principle is this:

="First String"&A1&"Next String"

If the string has quotes inside, double them

="He said "Please" but nobody heard him"&A1&"next string"

If the string has quotes at the beginning of the string, then you need the opening quote for the string and the double quote for the quote inside the string. Likewise for quotes at the end of the string: duplicate the quote in the string and then add the closing quote.

="""Please" - he said"&A1&"and she answered "OK."""