+ Reply to Thread
Results 1 to 6 of 6

Breakout multiple items from a single cell into their own row

  1. #1
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,020

    Breakout multiple items from a single cell into their own row

    Hello Excel Forum Folks:
    Can the Original Data P/N's and descriptions be broken out into their own row, when multiple supplied items requested are in a single cell? They are often separated by dashes, possible carrier return and possibly spaces. This will facilitate VLOOKUP's to bring in prices for those parts.

    See attached worksheet with data fields as supplied, and broken out fields as desired. (The broken out Desired Results fields would likely be placed on another tab.)

    Thank you for any ideas on this one. I think it'll be messy and maybe not worth the bother; it's usually a couple of hours to clean these up. Maybe there's a good way to do this.

    Pete
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,090

    Re: Breakout multiple items from a single cell into their own row

    I have no idea how to get the Price Ea.

    Please try Power Query
    select original data > Ribbon data > from Table > Advanced editor > paste below code

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-18-2020 at 01:33 AM. Reason: Attached wrong file

  3. #3
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,020

    Re: Breakout multiple items from a single cell into their own row

    Bo_Ry:

    Absolutely brilliant!!!!!!!!

    Thank you.

    Pete

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,020

    Re: Breakout multiple items from a single cell into their own row

    Hi Bo_Ry:

    Have a small quirk. Some of the P/N's do not have letters in them, just numbers: (such as 1047663). I should have included those in my example.

    Excel is generating this error:
    Please Login or Register  to view this content.
    I've tried to change the date type to something else, but the error persists. Any thoughts?

    EDITED NOTE: (I can place a TEXT qualifier in front of the P/N column in my table, copy, paste back as values, replace the original column and it works, but maybe there's a data type thingey that would do the trick in the code.)

    Many thanks if you could take another look.

    Pete
    Last edited by PeteABC123; 11-17-2020 at 05:28 PM.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,090

    Re: Breakout multiple items from a single cell into their own row

    Please add a step to change P/N to text

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,020

    Re: Breakout multiple items from a single cell into their own row

    Bo_Ry:

    Perfect. (Can I be you?) Thank you.

    Pete

+ 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. Pick multiple items from list and sort alphabetically in single cell
    By kawale in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-07-2021, 01:48 PM
  2. [SOLVED] Need help with associating multiple items to a single key for VBA dictionary.
    By omar23j in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2018, 06:20 PM
  3. [SOLVED] Pivot table or Chart, but data has multiple items in a single cell
    By Rilly in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-22-2018, 10:24 AM
  4. Transfer and consolidate data from multiple line items to within single cell
    By Noah14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2015, 10:43 AM
  5. Replies: 11
    Last Post: 08-27-2014, 02:05 PM
  6. Single to multiple items formula
    By werko in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-13-2014, 06:00 AM
  7. Replies: 0
    Last Post: 11-13-2012, 12:53 PM

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