Announcement

Collapse
No announcement yet.

Need an expert on MS Excel.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need an expert on MS Excel.

    Who's good with Excel?

    I've got a new website ready to launch for the shop but there's a line of HTML code that's part of each product description and this one line needs to be deleted from 2500 products. We've exported the entire product catalog to an Excel spreadsheet. This one line of code is exactly the same for each product and it's right at the end of each description. I'm wondering if there's a way I can tell Excel to delete the last 150 characters from the end of the text for each box inside a column.

    I'm trying to find an easy way of deleting this code, other than manually doing it to 2500 products.
    Special deals for JCF members on Jackson/Charvel, Suhr, Anderson, Nash, Splawn, Bogner, LSL, Ibanez, Diezel, Friedman, Bad Cat, 3rd Power, Dr. Z, ENGL and more. FREE SHIPPING! 0% FINANCING!

  • #2
    Someone will chime in who knows macros or VB script, but I use SQL a lot at work. If I had to do this, I'd import the data into a database (DB2, SQL, Access, etc.) and do an update with a substring function.

    update database.table set field = substr(field, start position, length)

    Then export back out to Excel or wherever it needs to go.

    Comment


    • #3
      And how long might something like this take?
      Special deals for JCF members on Jackson/Charvel, Suhr, Anderson, Nash, Splawn, Bogner, LSL, Ibanez, Diezel, Friedman, Bad Cat, 3rd Power, Dr. Z, ENGL and more. FREE SHIPPING! 0% FINANCING!

      Comment


      • #4
        Here's a simple excel-only formula:

        =LEFT([string to truncate], LEN([string to truncate]) - [number of chars to remove])

        for example, if one record is in column A1, you'd put this formula in B1 :

        =LEFT(A1, LEN(A1) - 150)

        Then copy that cell all the way down column B
        _________________________________________________
        "Artists should be free to spend their days mastering their craft so that working people can toil away in a more beautiful world."
        - Ken M

        Comment


        • #5
          I plugged this in but it didn't do anything.

          =LEFT(g17, LEN(g17) - 171)

          Am I doing something wrong?


          Originally posted by Axewielder View Post
          Here's a simple excel-only formula:

          =LEFT([string to truncate], LEN([string to truncate]) - [number of chars to remove])

          for example, if one record is in column A1, you'd put this formula in B1 :

          =LEFT(A1, LEN(A1) - 150)

          Then copy that cell all the way down column B
          Special deals for JCF members on Jackson/Charvel, Suhr, Anderson, Nash, Splawn, Bogner, LSL, Ibanez, Diezel, Friedman, Bad Cat, 3rd Power, Dr. Z, ENGL and more. FREE SHIPPING! 0% FINANCING!

          Comment


          • #6
            did you try a simple "find" and replace?
            (search for copy/paste the thing you want to get rid of) then in the replace box put in nothing.
            Just a thought
            "Wow,... that was some of the hardest rockin ever. Hardest to listen too."
            --floydkramer

            Comment


            • #7
              Originally posted by mattsmusiccenter View Post
              I plugged this in but it didn't do anything.

              =LEFT(g17, LEN(g17) - 171)

              Am I doing something wrong?

              Didn't you only want to remove 150 chars? That formula will remove 171 chars.
              _________________________________________________
              "Artists should be free to spend their days mastering their craft so that working people can toil away in a more beautiful world."
              - Ken M

              Comment


              • #8
                I counted and it's 171 characters that need to be deleted.
                Special deals for JCF members on Jackson/Charvel, Suhr, Anderson, Nash, Splawn, Bogner, LSL, Ibanez, Diezel, Friedman, Bad Cat, 3rd Power, Dr. Z, ENGL and more. FREE SHIPPING! 0% FINANCING!

                Comment


                • #9
                  Originally posted by nhspike View Post
                  did you try a simple "find" and replace?
                  (search for copy/paste the thing you want to get rid of) then in the replace box put in nothing.
                  Just a thought
                  That's exactly what I would have suggested ! Copy the section you want to have deleted and paste it in the "find" field, replace it with "blank" ! That should work !
                  uva uvam vivendo varia fit

                  Comment


                  • #10
                    Sounds easy... can I get a little more detail on the exact steps to do this? I thought I was good with Excel, apparently not good enough though!

                    Originally posted by Franx View Post
                    That's exactly what I would have suggested ! Copy the section you want to have deleted and paste it in the "find" field, replace it with "blank" ! That should work !
                    Special deals for JCF members on Jackson/Charvel, Suhr, Anderson, Nash, Splawn, Bogner, LSL, Ibanez, Diezel, Friedman, Bad Cat, 3rd Power, Dr. Z, ENGL and more. FREE SHIPPING! 0% FINANCING!

                    Comment


                    • #11
                      Originally posted by mattsmusiccenter View Post
                      I counted and it's 171 characters that need to be deleted.
                      I'm stumped. That formula is pretty straightforward.
                      _________________________________________________
                      "Artists should be free to spend their days mastering their craft so that working people can toil away in a more beautiful world."
                      - Ken M

                      Comment


                      • #12
                        Looks like this should work but it's not doing anything. Did I type the formula correctly?

                        =LEFT(g17, LEN(g17) - 171)
                        Special deals for JCF members on Jackson/Charvel, Suhr, Anderson, Nash, Splawn, Bogner, LSL, Ibanez, Diezel, Friedman, Bad Cat, 3rd Power, Dr. Z, ENGL and more. FREE SHIPPING! 0% FINANCING!

                        Comment


                        • #13
                          Go up to edit->Replace..., type what you want to delete into the Find field and leave the replace field blank. Then click Replace All. Much easier than messing with formulas or VBA.
                          Scott

                          Comment


                          • #14
                            Originally posted by mattsmusiccenter View Post
                            Looks like this should work but it's not doing anything. Did I type the formula correctly?

                            =LEFT(g17, LEN(g17) - 171)
                            Yeah it's right, at least for Office 2003. Make sure auto calculation is enabled (tools\options\calculation). Also make sure the cell with the formula is set to "general" format (right click/format cells).
                            _________________________________________________
                            "Artists should be free to spend their days mastering their craft so that working people can toil away in a more beautiful world."
                            - Ken M

                            Comment


                            • #15
                              I think the find and replace would be the easiest way to do this.

                              ctrl+f

                              copy the code that you want to get rid of.

                              I don't have excel on this computer but it's not too hard, there should be a replace tab or advanced tab.

                              Then just fill in the "replace with:' as a blank

                              http://support.microsoft.com/kb/288291
                              Last edited by anuske9; 03-13-2008, 02:40 PM.
                              -Adam

                              Comment

                              Working...
                              X