Iranian Dr. Excel posted a challenge on LinkedIn:
How can you split columns when there are multiple, different delimiters? Ex:
Joe+Lisa+Francine/Kathy
Rita&Sal&Gene
Samantha/Denise
This was interesting for several reasons:
1. It's very easy to do if you want a 1-and-done solution with just 3 delimiters. However,
2. If you want something truly dynamic, it involves solving 2 major problems with Power Query: splitting variable columns and merging all columns in a query.
3. I show an ugly but effective solution that I call The Elephant Through the Front Door Move.
First. When splitting columns with Power Query, a value gets hard-coded; e.g., if your initial split goes into 5 columns, the 5 is stuck, and if you later have 7 columns or 3 columns, that 5 is still there.
I found a solution to this here:
[ Ссылка ]
Second. Sometimes we want to merge all of the columns in a query, but there's no feature for that. But here is Power Query M-Code:
[ Ссылка ]
This was hard! But the solution I show is truly dynamic. If there are more delimiters in future data, they get picked up; if the number of columns grows or expands, Power Query will cooperate.
#SplitByDelimiters
#MergeColumns
#PowerQuery
For a list of my Excel courses at Lynda/LinkedIn:
[ Ссылка ]
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.
Website: [ Ссылка ]
My book: Guerrilla Data Analysis 2nd Edition
[ Ссылка ]
My old blog: [ Ссылка ]
