Posted: 1/7/2008 5:40:37 AM EDT
|
Question #1 I'm trying make a macro to copy column1 taking just the number part of the cells, then add a .jpg extension to fill column2 shown below. The a's and b's are not part of the image name. I tried many different ways, but can''t get it to work. Any ideas? column1...............column2 widget 200a..........200.jpg widget 200b..........200.jpg widget 210a..........210.jpg Question #2 I have text in a cell that I want to enclose with html characters for an online catalog description. Say for example I have; widget is 100lbs brown 120v I want to have it say; <p>widget is 100lbs</p><p> brown</p><p>120v</p> I can't just highlight text and add a <p> and </p> to the selected text. All the macros I have tried creating so far are just deleting the entire cell and replacing it with with <p></p>. I have even tried to insert just a single <p> into existing text with a macro, but the entire contents still get deleted and replaced. I would like to be able to create a few different macros to insert different tags, such as <h1>, <h2>, ect. Can this be done? |
|
Can it be done - yes. Depending on how exactly you want to use it, dictates how you create it. For your first question, the simpilest thing to do would be to put the formula 
into the cell next to the widgets, and changing the cell ref "A1" to whatever you need. This does, however, assume that the format of the widget name will always have the same spacing. If it's not a fixed width, but there will always be a space in front of the "image" name, and the last char will always be removed, this should work (it's broken into to cells, just to keep the damned thing from getting so long) Column 2 formula:
column 3 formula:
Basically, column 3 finds the last "word" in column 1, and colun 2 chops the last char off of the word found by column 3. Or, use the ffollowing to make a macro (it will create the jpg name one cell to the right of the selected cell - it has the same assumptions as #2 above.
(some of the comments are wrapping in here - make sure they aren't in VBA.) |
Is this always going to be in description/color/size format, or do you want to be able to highlight the content? If it's a fixed format, it's not that hard to break it apart in VBA. I've never attempted to grab a user-highlighted section of a cell and send that to VBA. Secondly - have you thought about breaking the the description pieces into seperate columns - it'd be much easier to address the discrete elements that way. |
You have no idea how much time that this will save me!! I have all the manufactures represented by 2 alphanumeric characters for much shorter image filenames. So I tweaked your excel mastery a little and came up with this for column 2; ="wi"&(LEFT(RIGHT(A1,4),3)&".jpg") In column 3, I need another unique name for a smaller thumbnail image and have this; ="wi"&(LEFT(RIGHT(A1,4),3)&"_t.jpg") I'll in the process of adding close to 8000 products in an online catalog for my wife's bridal/formal store and your formula will generate all the image filenames for these products. Here is a result of your formula at her site, Jilada Designs Thanks!!!! I'm sure the ARFCOM army could solve virtually any problem! |
|
I'm pretty good w/ Excel, but I'm having a mental block about this one for some silly reason. I'm trying to count how many of each state there are: COMPANY_____STATE ================ Widgets, Inc_____OH Colstat Corp_____CO Bing Ind_____CO Geroge Company_____OH Ft Collins Manhole Covers_____IA I want a quick formula that can tell me that there are 2 OH companies, 2 CO companies, 1 Iowa company, etc. My initial solution started down the path of an index/match to separate them but it's already too clunky. Suggestions? |
Glad to have helped  Did you manage to modify the formulas for your HTML tags? (BTW - given you're at 8,000 records of some sort already, and I'm assuming as you're an arfcommer, and therefore significantly more intelligent and succesful than most, the web store will be growing. This is a really good time to get everything into a true database - even if it's only Access, and break the fields apart as much as possible. It'll make your life so much easier in the long run.) |
Put it in SQL server?  (select count(distinct right(ColA, 2)) from ColA)The closest I've found in a strictly Excel solution is to put
at the base of column b, with both ranges adjusted to all of column B. (formula found using "excel count distinct items" in google.) ETA: After entering the second formula, it will only work if CTRL+SHFT+ENTER is hit. |
I had an old site that used Zencart and I just had too many problems with it. I did a CSV export of the site and have to manipulate the data to get it right for the current website. I'm using Joomla as the CMS, and a plug-in called Virtuemart for the on-line catalog. Virtuemart has a csv/Excel upload function that populates the mysql database. So...Excel is the best solution for now that I can come up with fixing the old CSV export and adding new products. I'm a Hybrid/Battery electric powertrain engineer by day, and a web geek for my wife at night. I just don't have enough time in the day for custom development. Once I finish with the initial upload to the server database, then I'll customize an Access database to generate the spreadsheet for upload to the server. #1 priority for now is to get product up and getting search engines looking at the site. Many of the manufactures do not/will not offer an electronic version or their product/pricing info to the retailers. I'm forced so scan them, use OCR software to put them in a spreadsheet, then upload the spreadsheet to the database. For the HTML tags, I'm just separating info by columns, adding tags with the & function, then combining them all into one column with another & function. |