How to Split Comma Delimited Cells into Multiple Rows in Excel While Keeping Original Rows

Discover how to efficiently split comma-separated values in Excel using Power Query without losing your original rows. Learn the step-by-step process to maintain your data's integrity.
---
This video is based on the question https://stackoverflow.com/q/70480706/ asked by the user 'akshay gadekar' ( https://stackoverflow.com/u/10974813/ ) and on the answer https://stackoverflow.com/a/70481119/ provided by the user 'akshay gadekar' ( https://stackoverflow.com/u/10974813/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Split comma delimited cell into multiple rows, keeping Original Rows as it is?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Split Comma Delimited Cells into Multiple Rows in Excel While Keeping Original Rows

When working with data in Excel, you may encounter situations where a single cell contains multiple entries separated by commas. You might want to split these entries into separate rows for better analysis while ensuring that the original formatting of your rows remains intact. This guide will guide you through the process of achieving this using Excel's Power Query feature, so you can maintain your original rows as they are.

The Problem

Imagine you have a dataset that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Using Power Query to split these values would typically generate new rows based on the comma delimiter, but it eliminates the original rows in the process. The expected outcome should look like this:

[[See Video to Reveal this Text or Code Snippet]]

The Solution

Here’s a step-by-step guide on how to split the comma-separated values and keep the original rows in place.

Step 1: Split Rows by Comma Delimiter

Use Power Query: Open your Excel workbook and navigate to the Power Query editor.

Import your data: Load your data into the Power Query editor.

Split Column: Right-click on the column containing the comma-separated values and select Split Column By Delimiter.

Choose Delimiter: Select Comma as the delimiter and opt for At each occurrence of the delimiter.

Click OK: This will create new rows for each entry in the original cell.

Step 2: Regenerate the Original Rows Above the New Split Rows

Copy Original Rows: After splitting, copy all the original rows that have not been altered.

Paste Above Split Rows: Paste the copied original rows above the rows generated from what you previously split.

Step 3: Sort the Data

Add a Filter: Click on the column header that contains your identifying numbers or IDs.

Sort: Use the sort option and choose to sort from smallest to largest.

Done: Your data should now appear just as you expected, with the original rows preserved and the new rows below them.

The Result

After following these steps, you will achieve the desired formatting where each entry is on its own row, while the original rows are still intact above. This method not only maintains a clean data set but also allows for easy reading and analysis.

Conclusion

Splitting comma-separated values in Excel while keeping the original data can be achieved efficiently using Power Query. By following the outlined steps, you can ensure your data remains intact while gaining the flexibility of viewing entries in separate rows.

Feel free to ask any questions or share your experiences in the comments below!

Видео How to Split Comma Delimited Cells into Multiple Rows in Excel While Keeping Original Rows канала vlogize