|
Help with very repetitive task in excel...is there an answer?
Hi all,
Ok, here's the challenge... I've got a csv database of text to upload (sql) to my website database. This database contains all of the information on books that I sell (approx. 1700) the only problem is, is that I've got two columns of the same text (book descriptions) that needs to be amended / revised to be the same. That's the long and relatively easier part of the job. Column A - Has the text description of the book ie. "Instructions & colour photos for a variety of socks for all the family". I want to amend this text and then copy and place in Column B - the trick is, column B has my html code ie. AUTHOR: KNITTING<brEDITION: Hardcover<brISBN: 1930500084<brPAGES: 96<brSIZE: 140mm x 180mm<br<br<brInstructions & colour photos for a variety of socks for all the family. As you can see, the text I want to replace (with what I've amended in column A) is at the end of this html code. So, short of copying and pasting nearly 2000 lines of text, is there something I can use / do that will copy text from column A and replace / paste at the end of the code in Column B at a specific point (ie. after the third <br point. This may look a bit weird, but the company I obtained the database from supplied it on a - what you see is what you get basis, in other words, if I want to use it, I've gotta fix it myself. I hope that explains - here's hoping someone can assist! Tim. |
Help with very repetitive task in excel...is there an answer?
Assuming your data is in Cols A and B as stated, then in C1 put the following
and then copy down =LEFT(B1,FIND("<br<br<br",B1)+11)&A1 Now edit the text as required in Col A and this will be reflected in Col C. When done, simply copy Column C and then paste special as values. Note, this assumes that in each line of html, you have only one section with 3 <brs and that that section is immediately prior to the text. The other option is simply to do an Edit / replace on <br<br<br* which will delete all text at the end of every statement. Then you can just use a simple concatenate in C1, eg =A1&B1, again copying and paste special as values when done. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tim Smith" wrote in message ... Hi all, Ok, here's the challenge... I've got a csv database of text to upload (sql) to my website database. This database contains all of the information on books that I sell (approx. 1700) the only problem is, is that I've got two columns of the same text (book descriptions) that needs to be amended / revised to be the same. That's the long and relatively easier part of the job. Column A - Has the text description of the book ie. "Instructions & colour photos for a variety of socks for all the family". I want to amend this text and then copy and place in Column B - the trick is, column B has my html code ie. AUTHOR: KNITTING<brEDITION: Hardcover<brISBN: 1930500084<brPAGES: 96<brSIZE: 140mm x 180mm<br<br<brInstructions & colour photos for a variety of socks for all the family. As you can see, the text I want to replace (with what I've amended in column A) is at the end of this html code. So, short of copying and pasting nearly 2000 lines of text, is there something I can use / do that will copy text from column A and replace / paste at the end of the code in Column B at a specific point (ie. after the third <br point. This may look a bit weird, but the company I obtained the database from supplied it on a - what you see is what you get basis, in other words, if I want to use it, I've gotta fix it myself. I hope that explains - here's hoping someone can assist! Tim. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004 |
Help with very repetitive task in excel...is there an answer?
Correction on last statement
simple concatenate in C1, eg =A1&B1 replace with simple concatenate in C1, eg =B1&"<br<br<br"&A1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ken Wright" wrote in message ... Assuming your data is in Cols A and B as stated, then in C1 put the following and then copy down =LEFT(B1,FIND("<br<br<br",B1)+11)&A1 Now edit the text as required in Col A and this will be reflected in Col C. When done, simply copy Column C and then paste special as values. Note, this assumes that in each line of html, you have only one section with 3 <brs and that that section is immediately prior to the text. The other option is simply to do an Edit / replace on <br<br<br* which will delete all text at the end of every statement. Then you can just use a simple concatenate in C1, eg =A1&B1, again copying and paste special as values when done. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tim Smith" wrote in message ... Hi all, Ok, here's the challenge... I've got a csv database of text to upload (sql) to my website database. This database contains all of the information on books that I sell (approx. 1700) the only problem is, is that I've got two columns of the same text (book descriptions) that needs to be amended / revised to be the same. That's the long and relatively easier part of the job. Column A - Has the text description of the book ie. "Instructions & colour photos for a variety of socks for all the family". I want to amend this text and then copy and place in Column B - the trick is, column B has my html code ie. AUTHOR: KNITTING<brEDITION: Hardcover<brISBN: 1930500084<brPAGES: 96<brSIZE: 140mm x 180mm<br<br<brInstructions & colour photos for a variety of socks for all the family. As you can see, the text I want to replace (with what I've amended in column A) is at the end of this html code. So, short of copying and pasting nearly 2000 lines of text, is there something I can use / do that will copy text from column A and replace / paste at the end of the code in Column B at a specific point (ie. after the third <br point. This may look a bit weird, but the company I obtained the database from supplied it on a - what you see is what you get basis, in other words, if I want to use it, I've gotta fix it myself. I hope that explains - here's hoping someone can assist! Tim. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004 |
Help with very repetitive task in excel...is there an answer?
Hi Ken
You are a legend!! This worked really well, and saved me many, MANY hours of labour. Thankyou very much for your help on this, Tim *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Help with very repetitive task in excel...is there an answer?
hehehehe - You can come again :-)
Seriously though, you're welcome and appreciate the feedback. Remember though - Anytime you are going to edit or blow away data - Backup Backup Backup!!!!!! -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tim Smith" wrote in message ... Hi Ken You are a legend!! This worked really well, and saved me many, MANY hours of labour. Thankyou very much for your help on this, Tim *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 |
| All times are GMT +1. The time now is 08:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com