Find and replace

**lnjsports** · 06-09-2009, 05:06 PM

I am a novice in excel so please bare with me.

I was wondering if it is possible to do this unique find and replace that is explained below.

I have many columns with data from our database. I have one column that has my html layout in it. I want to find within the html text data and replace it with data from another column for that row.

Example: We have [[manufacturer]] in the html area that we want to replace with data from our column called Manufacturer. So, it will replace the text in the html named [[Manufacturer]] with that rows Manufacturer data we have.

Is that possible to do?

**Paul** · 06-09-2009, 05:56 PM

Hi Injsports,

I'd recommend uploading a copy of your workbook (minus any sensitive data) so we can take a look. In the workbook be sure to have an example (or a few) of what your data looks like now, and how you want it to look afterward.

Thanks!

**lnjsports** · 06-09-2009, 07:38 PM

Thank for replying to my post to see if this is possible.

I attached a two row sample of my data. My full workbook is very large with over 10,000 items with a lot more columns then what is showing. I just deleted a lot of the columns that are not really associated with what my request is about.

If you look at the column (C) Template, you will see the sample template html that I provided. In the html, you will see text with [[ ]] around text. That is the text that I would like to replace with data from the associated column. ( [[PRODUCT_NAME]] with PRODUCT_NAME data ) I want to do this for each [[ ]] in the template html.

Does this make sense?

**shg** · 06-09-2009, 08:16 PM

Maybe like this:

Sub x()
    Dim s       As String
    Dim cell    As Range

    s = "[[" & Range("E1").Text & "]]"

    For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp))
        cell.Replace What:=s, Replacement:=Cells(cell.Row, "E").Text, _
                     LookAt:=xlPart, MatchCase:=True
    Next cell
End Sub

**martindwilson** · 06-09-2009, 08:18 PM

="<table width=""100&#37;"" border=""1"" cellspacing=""12"" cellpadding=""4"">
  <tr> 
    <td colspan=""2"" align=""center""><p>[["&A2&"]]</p>
      <p>[["&B2&"]]</p></td>
  </tr>
  <tr> 
    <td width=""450"" align=""center"">[["&L2&"]]</td>
    <td width=""56%"" align=""left"" valign=""top""><p>Manufacturer: [["&E2&"]] 
      </p>
      <p>Description:<br>
        [["&D2&"]]</p>
      <p>Size: [["&G2&"]]</p>
      </td>
  </tr>
  <tr> 
    <td colspan=""2"" align=""center""><p>- Huge Blow Out Sale -<br>
        Suggested Retail Price: [["&F2&"]]</p>
      <p>&quot;BUY IT NOW FOR&quot;<br>
        [["&I2&"]]<br>
      </p></td>
  </tr>
</table>"

sort of works but its nor easy to see.
youd be better of using mail merge with word using excel as your source to do this.
word is far better for this html editing than excel

**lnjsports** · 06-09-2009, 08:24 PM

Thank you SHG for that information. Please remember what I stated at the start of the topic. I am an novice here. I know that for many if not all other members here, they totally understood what that code means and does, but, I just said, WOW!

How would I go about in performing the code you just suggested.

**lnjsports** · 06-09-2009, 08:25 PM

Mail Merge? Never used it.

**martindwilson** · 06-09-2009, 08:36 PM

well if you using office you should really look into using word and mailmerge, part of the same toolbox really!!!!!!!
essentially put template in word , make all those bits in [[ ]] merge fields and it would pull the data from excel
creating a nice page of html from each row in excel

**lnjsports** · 06-09-2009, 09:09 PM

Thank you MartinD,

I just tested out the Mail Merge which it did do what I wanted for giving me the HTML with all fields filled in. BUT, how do I associate each new html with that row on my excel file?

Basically I want to have the template column or even a new column with the new html in it because then I have to use some of the columns not shown with the new html in an upload.

**martindwilson** · 06-09-2009, 09:24 PM

ah ! i was assuming you just needed to generate the html and would use the word doc for upload.

**lnjsports** · 06-09-2009, 09:26 PM

That mail merge did do the trick of what I wanted but then I would have over 10,000 of this html in word that I would have to manually put back into my excel file.

So, since that will not work, are there any other possible ways?

**martindwilson** · 06-09-2009, 09:45 PM

trouble is its so large you cant see whats in it because the formula bar view goes over it!
but ive had a look and if you put function i posted earlier in template cell and drag down it should work
the attached word doc shows whats cell c2(page1) and c3 (page2) after puting function in c2 and dragging down

**lnjsports** · 06-09-2009, 10:01 PM

WOO HOO!

I finally figured out to use the code that SHG gave me above to make it work.

With the code that he supplied, do I need to set up a newly edit module for every find and replace I do? Or is there a code that would do all of them?

**lnjsports** · 06-09-2009, 10:12 PM

Hey SHG,

That code really worked great for the sample you gave me. I then tried editing the code to do another column but it came up with an error. I was doing the [[Description]] text with the column (D) Description. The column has some html style text in it. I am not sure if that matters.

This is the code I edited for that one:

Sub x()
    Dim s       As String
    Dim cell    As Range

    s = "[[" & Range("D1").Text & "]]"

    For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp))
        cell.Replace What:=s, Replacement:=Cells(cell.Row, "D").Text, _
                     LookAt:=xlPart, MatchCase:=True
    Next cell
End Sub

**shg** · 06-09-2009, 10:36 PM

Post the workbook that doesn't work.

Thread: Find and replace

Thread Tools

Display

Find and replace

re: Find and replace

re: Find and replace

re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions