Forum Discussion
Multiple Data in Single Cell with One Corresponding Value In Different Cell
(sample attached)
#1 Format your data range as Table
#2 Click somewhere in the Table
#3 Go to Data (tab) > In Get & Transform Data group click From Table/Range (Power Query Editor opens)
#4 (not mandatory) delete "Changed Type" in APPLIED STEPS
#5 Select column [BMO]
#6 Click on Split Column > By Delimiter
- Check the determined delimiter is OK ortherwise adjust it
- Click Advanced options > Split into: Rows > OK#7 Click Close & Load at the top left corner of the window
Hi MansoorSadat
Just to clarify things...
1 Does the following reflect what you expect to acheive where data in B:C is what you currently have an E:F where you want to land?
2 What version of Excel do you run and on which OS (Windows, MacOS...)?
3 Are your data already https://www.bing.com/ck/a?!&&p=bea58f081ee2969dJmltdHM9MTY3MDQ1NzYwMCZpZ3VpZD0zZTg3MzRhZS1iNjU5LTZlYzItMWNlMC0yNmMzYjc3NzZmZGMmaW5zaWQ9NTE3OQ&ptn=3&hsh=3&fclid=3e8734ae-b659-6ec2-1ce0-26c3b7776fdc&psq=excel+format+as+table&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1?
- Hey L z. - Thanks for responding to me.
1. Yes this is exactly what I need.
2. Excel 365
3. Data is not in a table, but can convert if needed...
Thanks
Mansoor(sample attached)
#1 Format your data range as Table
#2 Click somewhere in the Table
#3 Go to Data (tab) > In Get & Transform Data group click From Table/Range (Power Query Editor opens)
#4 (not mandatory) delete "Changed Type" in APPLIED STEPS
#5 Select column [BMO]
#6 Click on Split Column > By Delimiter
- Check the determined delimiter is OK ortherwise adjust it
- Click Advanced options > Split into: Rows > OK#7 Click Close & Load at the top left corner of the window
- Thanks you so much! You are fantastic 🙂 !!! So good to learn something new. I appreciate sharing your knowledge.
