+ Reply to Thread
Results 1 to 6 of 6

Returning 2 Parts of a Cell as One part

  1. #1
    Registered User
    Join Date
    10-11-2007
    Posts
    4

    Returning 2 Parts of a Cell as One part

    I have a unique problem that I haven't seen anywhere on the boards, although i will keep looking. It involves data from the GFK database.

    GFK sends me data weekly regarding product sell through, however their products codes are irritatingly different from ours.. in this way.

    Where OUR product codes are written "LA32R81BDX", their product code for the same product will be written "SAMSUNG /LA 32R81BDX". The blank spaces are always random, and I had the epiphany of using the "trim" function but it leaves a blank space between the product code and doesn't remove the "Samsung /" at the front either.

    What I can do is set up a list of Suppliers/Brands as a point of reference to pull out that information from the codes - to return that data to one cell, but how do I join the product code and remove the brand to be returned to another cell?

    We have no idea :\ The data 100% has a / before the code begins, maybe this helps. I'm playing around in excel right now to try and get it to work. Maybe if I set up the supplier list I can have a sort of "Remove (concatenate("supplier",/) but this doesn't solve the pushing together of the rest of the word. A super "trim" would be awesome about now.


    ANY help is appreciated I'll keep checking this page throughout the days.

    edit***
    On a side note, can a vlookup return part of the same cell? I think thats what I need. I have a supplier list which looks up that supplier (with a wildcard following if possible) so like "vlookup("sunbeam*",$a$1:$b$5, (in this part do the squish together of everything after the /). Dunno if thats possible.
    Last edited by Stripey; 10-11-2007 at 02:36 AM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    Select column of data > Ctrl + H > in the find box press the spacebar once (for space) and / and in the replace box enter /. Then replace all

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    10-11-2007
    Posts
    4
    I knew there had to be a simpler way Thats awesome mate makes both seperating the brand and the product code a one hit macro. The only thing left is the random spaces in the middle of the product codes. Any suggestions? trim leaves on space always unfortunately. Again, thanks for that.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Add to a macro as you say

    it's same again but this time

    Find = spacebar
    Replace leave empty

    Please Login or Register  to view this content.
    VBA Noob

  5. #5
    Registered User
    Join Date
    10-11-2007
    Posts
    4
    Haha Mate you've just shown me how to do something that 3 years worth of other people haven't been able to do. Props. It worked like a charm.

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad it helped

    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1