Results 1 to 14 of 14
  1. #1
    Full Member jazzylee77's Avatar
    Join Date
    February 19th, 2005
    Posts
    199
    Excel find and replace question
    I'm using excel 2002 to prepare my datafeeds.

    One feed has product names that are prefixed with its product code. This makes for bad labeling with webmerge.

    examples:

    SKH26R 450eg Skewed widget Hangers
    M034 4pk Easy widget Trap
    337019 widget Oil

    another column has the SKH26R, M034,337019 codes also.

    My goal is to remove these codes from the product name, either by removing characters up to and including the first space or maybe a find and replace finding the value from the code column and replacing with blank in the product name column.

    If excel 2002 can't handle this, maybe someone could point the way to another solution?

  2. #2
    Internet Cowboy
    Join Date
    January 18th, 2005
    Posts
    4,662
    Open it in Word and record a Macro to find the first space in each line and delete everything before it or insert a tab.... ^t...or whatever you use to delimit your fields in the document. Then save the document and open it in Excel.


  3. #3
    Internet Cowboy
    Join Date
    January 18th, 2005
    Posts
    4,662
    Or save that column as its own document and open it in Word and replace all spaces with a tab...^t
    Then open it in Excel, delete the columns you want gone, then use a command like =(a1&" "&b1&" "&c1) to combine the cells back together again. When combining 3 columns, this formula would go anywhere past column C. You can copy it down for as many rows as you have.
    Then, as long as you did not mess with the way it is sorted, once you have it back together, copy it back to the original document.


  4. #4
    Full Member jazzylee77's Avatar
    Join Date
    February 19th, 2005
    Posts
    199
    Thanks. I get it! Okay..had to read it twice...but I get it. I've never used macros in word, but now that I have a good reason I'll dig right in.

  5. #5
    Crazy like a fox suzigeek's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,096
    OR use text to columns feature in the Data menu and choose delimited and choose other and put a space in the box and it will convert everything between the spaces into seperate columns. Then you can go back and join the columns you want together again.

    Basically the same as Uncle Scooters solution but you dont have to go back and forth between word.

    (btw I love text to column function for using on breaking up categories that I get from a merchant all listed in the same column)
    Suz~~GearGirl~~

  6. #6
    Full Member jazzylee77's Avatar
    Join Date
    February 19th, 2005
    Posts
    199
    I like that one suzigeek. I'll use text to columns next time. I should have thought of that since I do use it to break up cj categories!

  7. #7
    ABW Ambassador Nature Boy's Avatar
    Join Date
    January 18th, 2005
    Location
    Tennessee
    Posts
    1,423
    I'll base this on only having 2 columns: NAME (Column A) and SKU (Column B)
    1. Create a third column (C1) and name it NAME2
    2. In cell C2 put this: =SUBSTITUTE(A2,B2,"",1)
    Example (Brackets use to demonstrate what is in the box):
    Cell A2(NAME) = [12345 Test Item]
    Cell B2(SKU) = [12345]
    Cell C2(NAME2) = [ Test Item] (notice the space before the text)

    I'm using Excel 97, but I would think this would work in 2002.

    Hope this helps.
    Scott
    If you can't dazzle them with brilliance, then baffle them with bulls#!t
    Don't tell me that you'll do it... SHOW ME.
    Just because everyone else is drinking it is no reason for me to drink the KOOL-AID.

  8. #8
    ABW Ambassador buy_online's Avatar
    Join Date
    January 18th, 2005
    Location
    Richmond, VA
    Posts
    3,234
    Just like Scott has in his example above (great eample btw), you'll need to add a column for each piece of the original column you will end up breaking out.

    So, be sure to insert a few blank columns to the right of the column you want to break up. If not, you risk losing any data you have in those existing columns. You should get a warning about it, but it is a good idea to add a few extra columns.

    Once you have the data split out (with the Text-to-Column feature), you can go back and easily delete any extra columns you don't need. I'm using Excel 2000, and it works in that version too.

    You can find more info about handling Excel and data feeds on this page:
    http://www.web-procreate.com/wm-feed-prepare.htm

    Fred

  9. #9
    Member tsmgroup2's Avatar
    Join Date
    January 18th, 2005
    Location
    New Providence, PA USA
    Posts
    155
    Is there an easy way in Excel 2003 to swap out "youraffid" for the real "#####" in each cell from a datafeed? If so, can you tell me here, please?
    Thanks.

  10. #10
    Moderator BurgerBoy's Avatar
    Join Date
    January 18th, 2005
    Location
    jacked by sylon www.sylonddos.weebly.com
    Posts
    9,618
    Talking
    Normally - when I try to do a find and replace in excel It tells me that the formular is too long to do it.

    Something that will work - Copy the column that you want to replace your affiliate number in.

    Paste it into note pad.

    In notepad highlite youraffid and copy it. Go to edit - replace. Put youraffid into - find. In replace put your real affiliate id 1234567.

    Click on replace all and you will have your affiliate id where you want it.

    Go to edit - select all - while still in notepad. Copy the new information with your affiliate id in it now.

    Go back to excel. The colums you copied earlier will still be highlited. Click paste and you now have the column with your affiliate number in it.

    It will still be right and matched up with the correct row just like it was when you recieved the feed.

    Click save and you're in business.

    Hope I explained this so you know what I'm talking about.

    Vietnam Veteran 1966-1970 USASA
    ABW Forum Rules - Advertise At ABW

  11. #11
    ShareASale President/CEO and ABW Veteran Brian - ShareASale's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,657
    The original question... assuming the string was in cell A1 of the spreadsheet...

    if you create a cell next to it with the following formula it should give you the remainder of the string after the first space.

    =RIGHT(A1,(LEN(A1) - (SEARCH(" ",A1))))

    On the second question...just highlight the column where the "youraffid" is, and then go to "Edit --> Find --> Replace" and enter the appropriate info...
    Thanks,

    Brian Littleton
    President/CEO - ShareASale.com, Inc.

  12. #12
    ABW Ambassador buy_online's Avatar
    Join Date
    January 18th, 2005
    Location
    Richmond, VA
    Posts
    3,234
    Quote Originally Posted by tsmgroup2
    Is there an easy way in Excel 2003 to swap out "youraffid" for the real "#####" in each cell from a datafeed? If so, can you tell me here, please?
    Thanks.
    Sure!

    Copy the text you want to remove to the clipboard.
    Now, select the column you have your links in (by clicking the top of the column)
    Press "CTRL+H" in order to bring up the Search and Replace window.
    Paste the code from the clipboard (this is the code you want to remove) in the "Find What" field.
    Now, go and copy "youraffid" from where ever you have that, and paste it in the "Replace With" field.
    Now click on "Find Next" Excel should jump immediately to the first link it finds in the column.
    Finally, click on "Replace All"

    You're done. I would recommend reading my page on Preparing your feed from the link in my post above (always good to read things before asking questions ).

    What you are trying to do is something that you must be comfortable with in order to handle Data feeds. There are also many free tools out there that can do this for you as well, including AMWSO's great affiliate tools.

    If you have "descriptions" in your feed, and they exceed the characters that Excel wants to deal with, then follow Burger Boys' post. Excel S&R normally chokes on large amounts of text. I bring that stuff out, work on it, and throw it back in again - Viola!

    Fred

  13. #13
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    I use csved for that. Much less of a headache LOL

  14. #14
    Full Member jazzylee77's Avatar
    Join Date
    February 19th, 2005
    Posts
    199
    Quote Originally Posted by jackson992
    I use csved for that. Much less of a headache LOL
    I just downloaded csved and what a great tool! I had a long description column that even notepad couldn't handle. I've only glanced at the other edit options. I guess I'll still need excel, but csved takes care of the big file problems.
    Last edited by jazzylee77; February 6th, 2006 at 06:01 PM.

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Need find replace software
    By PetsWarehouse.com in forum Programming / Datafeeds / Tools
    Replies: 14
    Last Post: April 17th, 2007, 07:53 AM
  2. Excel Question
    By ~Michelle in forum Midnight Cafe'
    Replies: 2
    Last Post: January 21st, 2004, 02:02 PM
  3. Webmerge Find & Replace Problem
    By Tiebreaker in forum WebMerge (Fourthworld.com)
    Replies: 1
    Last Post: January 18th, 2004, 12:58 PM
  4. Find and Replace
    By cazzie in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: December 13th, 2002, 03:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •