No announcement yet.

MS Excel Question

  • Filter
  • Time
  • Show
Clear All
new posts

  • MS Excel Question

    I'm using Excel (2003) to upload my products. My question ....
    Let's say I have 100 products. I have an existing spreadsheet with a column for "image filename". I sthere a way to add to the front of each item in that column the same text with a command or do I have to do each one?

    EXAMPLE: Each one is basically "ImageName.jpg" and I want to quickly add "assets/images/" to the front of each one so it reads "assets/images/ImageName.jpg"

    I think I have searched everywhere in excel, but I could not find this. Could be right under my nose but I just missed it!!! Any help would be greatly appreciated.

  • #2
    You can downlod open office for free

    Put assets/images/ in 1 column
    and your imagename.jpg in the next column.

    then merge columns
    the result will be assets/images/ imagename.jpg

    next we will need to remove the space after the /
    find and replace /space with / (use the space bar in place of "space")

    That's how I do it, there may be an easier way in excel

    But if you like me and eager for an answer now then there you go.
    Go Live Industries


    • #3
      Thanks GoLive, as always you have great wisdom and solutions. Unfortunately, I am trying to stay with Excel. I tried your suggestion (the other night before posting this) in Excel, but it didn't work as expected.

      A couple of years ago I tried OpenOffice and for some reason or other, it didn't fit my style. Which is really ironic because I actually hate most of MS's software!! Go figure. I just may have another go at OpenOffice.

      That being said, I think I may have found some sort of option (by actually searching deeper in the forums!) in Excel that may work. It's called the "Concatenate feature". Is that even a word!!! LOL I guess I will look into this a bit more.


      • #4
        If you have two columns you can make a formula for a third column that will give you what you want.
        Say cell A2 has assets/images cell B2 has widget.jpg

        go to cell C2 and type:

        This will give you assets/images/widget.jpg

        then click in cell c2, grab the handle (small black box) in the right bottom corner and drag the box down for as many rows as you have. this will copy the formula down and create your image links for the remainder of the rows.

        Once you save this Worksheet in CSV (save as csv), you can delete columns A and B without affecting column C.

        We use this formula type all the time for product spreadsheets. You can have 3 columns for description, one for size, one for basic description, and one for instructions. then combine all three in the extended description column using a formula such as:

        note that you enclose any text or code in quotations marks. Use & to combine cells or "text" and cells.

        Probably an Excel expert might have an easier way of doing this, but this works wonders.


        • #5
          Sorry for the late reply and Thank you! - that worked like a charm.
          I learned something new! I never new about the "little black box in the bott corner (Auto Fill)". That has been very helpful.


          • #6
            Originally posted by elightbox View Post
            Probably an Excel expert might have an easier way of doing this, but this works wonders.
            I do the same thing for my 3d updates. It's a great way to do images and metatags without a lot of fuss.


            • #7
              We use the merge feature as well. Work GREAT! Is a little tricky to use, but it works. Just a few extra tips.
              Use fill down, to copy formulas down through all your products.
              Use search and replace to fix errors, remove spaces or strange characters or whatnot.
              Use the PROPER function to properly capitalize text.
              Use the COPY SPECIAL command and copy and paste "VALUES" to a third comlumn when using currency formulas that don't actually contain proper number formatting.
              Hope this helps.


              • #8
                Mark, great tips. I have not used the Merge Down, but will be looking for it.

                One more note about the "Auto Fill" little black box:
                use it in combination with the CTL button to change the default behavior: Copy the same, or increment.
                Be careful, Excel decides the default behavior depending on the cell and column content. Sometime it copies the same, sometimes it increments. I have made major mistakes not paying attention!


                • #9
                  Originally posted by elightbox View Post
                  One more note about the "Auto Fill" little black box:
                  use it in combination with the CTL button to change the default behavior: Copy the same, or increment.
                  Good info! I didn't realize the CTL key would do that. Thanks! :D


                  • #10
                    One thing I did find out was using FIND REPLACE would not work if the cell contained a function. It came back as an error. So I had to COPY then PASTE SPECIAL (by value) in a third colum to strip the formula. I then CUT PASTED back into the original column. This allowed me to edit the text and do a FIND REPLACE to get the dashes, etc. It also allowed me to edit the cell value.

                    I looked everywhere in excel, but found no other way to remove the formulas but keep the values. This would save some steps. Maybe there is something I am missing?


                    • #11
                      I've always used an additional column and then Pasted Special (Values), just like you did.


                      • #12
                        Ok, so far everything has worked great merging columns in Excel (thanks to the great help here). But, I have a new problem that I just cannot figure out.

                        From my current (old, not 3DCart yet) I have two columns in the spreadheet. One is "Meta Description" and the second is "Meta Keywords". I figured I can use these two columns to create the proper "metatags" column for my product upload to 3D. To start I manually created a product in 3D and manually inserted the proper meta info using the Tag Wizard. I then downloaded the product file to see how the metatags column was structured. I saw:
                        <META NAME="DESCRIPTION" CONTENT="description text here"> <META NAME="ABSTRACT" CONTENT=""> <META NAME="KEYWORDS" CONTENT="keywords here">

                        I cannot get the two columns to combine along with the needed text. I tried just about every combination in the formula (using & and "), but nothing is working!

                        Any help would be greatly appreciated. I sure would hate to have to do each products meta info manually!


                        • #13
                          for quote marks that are not actually part of the formula you got to double them like this

                          ="<META NAME=""DESCRIPTION"" CONTENT="""&C32&""">xxxxxxxxxx

                          where C32 would have the Description content

                          ""DESCRIPTION"" double quotes
                          """&C32&""" triple quotes


                          • #14
                            You don't need to combine them or manipulate them in any way to create your Meta Tags. 3dCart can automatically generate these for you, just fill in the appropriate fields with the data you want to use.

                            The Meta Description tag comes from the "Short Description" field on the product data record on the Information tab.

                            The Meta Keywords tag comes from the "Keywords" field on the product data record on the Information tab.

                            Don't enter any HTML in the fields, just the values you want the Meta Tag to display. Try it on one product, then go to the product page and view the source to make sure it looks like you want it to look. :)


                            • #15
                              An Easier Way

                              Check out the concatenate function. Very handy! Combine, drag & fill. Done. Once filled, use Find & Replace.