+ Reply to Thread
Results 1 to 10 of 10

Single Cell Data Split (not normal delimiter) - possible ?

  1. #1
    Registered User
    Join Date
    03-30-2011
    Location
    new york
    MS-Off Ver
    Excel 2003, 2010, 2013
    Posts
    20

    Post Single Cell Data Split (not normal delimiter) - possible ?

    Hi all excel world,

    I am a long time Excel user and forum reader and this is my first question/post for some help and assistance.

    I have a data set that is in a single column and I am trying to break it out into multiple columns based on certain criteria. And I understand that this may not be possible so I'm reaching out here.

    I was trying to do it based on an =LEFT( or =RIGHT( but I have been unsuccessful in getting the columns in the right order.

    I was wondering (and hoping) to be able to find a way to break column A down into columns B, C, D, E, F (maybe using a word match in a macro or VB script or a probably complicated IF statement?)

    Where
    DATASET Device Storage Color Network Model
    iPhone 5 16GB white - T-Mo - ME487LL/A iPhone 5 16GB white T-MO ME487LL/A
    PG58100 Sensation 4G Sensation 4G PG58100
    PC10100 G2 titanium G2 titanium PC10100
    SGH-T999 Galaxy S 3 16GB white Galaxy S 3 16GB white SGH-T999
    SM-G900T Galaxy S5 16GB black Galaxy S5 16GB black SM-G900T
    SGH-T889 Galaxy Note 2 16GB titanium Galaxy Note 2 16GB titanium SGH-T889



    Some other examples are:
    iPhone 5 16GB white - T-Mo - ME487LL/A
    iPhone 5s 16GB Gold - T-Mo - ME325LL/A
    iPhone 5s 64GB Silver - T-Mo - ME330LL/A
    PG58100 Sensation 4G
    PC10100 G2 titanium
    0P6B700 One M8 gold - Sprint
    D801 G2 white
    D801 G2 black
    D415 Optimus L90 grey
    SGH-T999 Galaxy S 3 16GB white
    SM-G900T Galaxy S5 16GB black
    SGH-M919 Galaxy S 4 16GB white
    SGH-T889 Galaxy Note 2 16GB titanium
    SGH-T989 Galaxy S 2 white

    Thank you for reading this and for any assistance and help.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    Hi Dice,

    Welcome to the Forum!: - Replace the red numbers to suit your situation

    Please Login or Register  to view this content.
    If you haven't used macros before you'll need to go to:
    File- options - trust center -trust center settings - macro settings ,
    the second option down (disable all macros with notification)

    Then - Copy the code to the clipboard

    Open your Workbook

    Press ALT + F11 to open the Visual Basic Editor.

    Select "Module" from the Insert menu

    Type "Option Explicit" then paste the code under it

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    *Be sure to save the book with the code as Macro-Enabled
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    03-30-2011
    Location
    new york
    MS-Off Ver
    Excel 2003, 2010, 2013
    Posts
    20

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    Thank you for this- This certainly simplifies things.

    Is there perhaps a way to keep certain content together and not separate everything in separate cells?
    So, what I'm looking for is:
    DATASET Device Storage Color Network Model
    iPhone 5 16GB white - T-Mo - ME487LL/A iPhone 5 16GB white T-MO ME487LL/A
    SGH-T999 Galaxy S 3 16GB white Galaxy 16GB white SGH-T999

    But when I run your script I get this where iphone 5 is separated and should remain together same with Galaxy S 3 (if this is even possible):
    DATASET Device Storage Color Network Model
    iPhone 5 16GB white - T-Mo - ME487LL/A iPhone 5 16GB white T-MO ME487LL/A
    SGH-T999 Galaxy S 3 16GB white SGH-T999 Galaxy S 3 16GB white
    Last edited by dice1976; 11-27-2015 at 12:29 PM. Reason: table fix

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    Need to see your desired result in a WORKBOOK.
    Here's the code to start with
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-30-2011
    Location
    new york
    MS-Off Ver
    Excel 2003, 2010, 2013
    Posts
    20

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    I am going to share an example data set that I think would make it easier.

    Please see attached and let me know if this is even possible to do in excel.

    I have multiple sheets with 1000s of rows to do this for and I'm just stuck.

    Also you can see here 2 different Descriptions 1 and 2 that I am trying to break out data into separate cells.

    Some data matches some does not.

    Thanks.

    data set example.xlsx

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    This is for the provided data
    Please Login or Register  to view this content.
    Last edited by jindon; 11-27-2015 at 07:42 PM. Reason: One of the pattern has been changed.

  7. #7
    Registered User
    Join Date
    03-30-2011
    Location
    new york
    MS-Off Ver
    Excel 2003, 2010, 2013
    Posts
    20

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    WOW! that's amazing. I didn't think this was possible. you saved me countless hours!!

  8. #8
    Registered User
    Join Date
    03-30-2011
    Location
    new york
    MS-Off Ver
    Excel 2003, 2010, 2013
    Posts
    20

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    So- is there any way to use a list range or something like that?

    For instance, I see in this line:
    .Pattern = "\b(T-Mo|ATT|Verizon)\b"
    I left out a network, sprint-

    And for the colors
    .Pattern = "\b(silver|black|Gray|white)\b"
    I left out blue and light brown (and when I added Light Brown manually, no colors came in the column- I think because of the space between light and brown)

    Is there some way to put (I obviously tried this on my own and was unsuccessful) sometihng like this :
    .Pattern = "\b(ColorList)\b"
    or
    .Pattern = "\b(NetworkList)\b"
    where ColorList contains the list of colors on a separate sheet within the workbook?

    And - I don't know why, but this one from sprint - leaves out this part MG6C2LL/A (even after I modified and added sprint in the network line and gold in the color line)
    iPhone 6 16GB Gold - Sprint - MG6C2LL/A


    Lastly- is there a way to have it ignore case and put uppercase for everything ?

    Thanks soooo sooo much for all this help - you have no idea how great this is for me.

    P.S. Other than that - on a spreadsheet with 4200 lines of this, it's amazing to see it perfectly broken out correctly for once
    Last edited by dice1976; 11-28-2015 at 02:39 AM. Reason: spelling

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    That's possible and the way it should be....

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Single Cell Data Split (not normal delimiter) - possible ?

    Add to the List in "Lists" sheet as needed.
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Split data from single cell into multiple records
    By Philangr8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2015, 07:55 PM
  2. Replies: 1
    Last Post: 01-05-2015, 08:29 PM
  3. Replies: 3
    Last Post: 11-28-2011, 03:08 PM
  4. Split contents of a cell into several cells (space as a delimiter)
    By ramky79 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-03-2011, 04:50 AM
  5. Want to split the data in single cell to multiple column
    By pradeepdeepu_001 in forum Excel General
    Replies: 4
    Last Post: 02-02-2010, 09:11 AM
  6. Split Cell by Delimiter, Move to New Row...
    By jpfulton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2008, 03:43 AM
  7. Split Cell by Delimiter, Move to New Row...
    By jpfulton in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 09-08-2008, 03:53 PM

Tags for this Thread

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