When faced with a challenge in Microsoft Excel it’s rare that two very different approaches can both be equally amazing! In this post I’ll review two completely different solutions from Oz du Soleil and David Hager. 

The Challenge

Oz was contacted by someone who wanted to re-arrange some data. Take a look:

Per row, every 3 area code characters should be stacked on top of each other. In Excel we use a carriage return (like on a typewriter) to stack things in the same cell.

Why does his friend want this odd structure? It doesn’t matter. Oz just wanted to help out. But there was something more…

Oz’s Solution

Oz used Get & Transform (aka Power Query) to solve this. But why?

Get & Transform is the most incredible new feature in Excel since VBA or Pivot Tables. You can use it to clean, unpivot, merge data into the perfect form. You don’t want to build an Excel model on top of oddly shaped messy data. Run your raw data through Get & Transform!

Watch Oz’s Video

Oz loves sharing his knowledge and each video is a production. Watch it here!

If you can’t watch the entire video watch the best part!

• Oz had split each group of 3 numbers in separate columns
• merged them back into a single column with comma delimiters
• STACKING MAGIC: replace values / advanced options / special characters / line feed 
• export to Excel, wrap text and done!

Something More

Oz is helping his friend but also using this challenge to play around and learn! Oz needs these challenges as much as his friend needs a solution. It’s the same for me!  🙂

David Hager’s Solution

When I was watching Oz’s video last fall I remember thinking “I could solve this using TEXTJOIN function with CHAR(10)“. This is exactly how David solved it!

TEXTJOIN function!

Just as Oz is using his friend’s requirement to learn Get & Transform, David is using it to experiment with the TEXTJOIN function.

When a new function is introduced I’m cautious of backwards compatibility issues but I study what it can do so I’ll be ready. TEXTJOIN is one of the most useful new functions that Microsoft has introduced in years!

David’s TEXTJOIN Formula Solution

=TEXTJOIN(CHAR(10),,MID(A1,{1,4,7,10,13},3))   Read David’s post. 

Such a brilliant formula. How does David’s formula work?

TEXTJOIN has the superpower ability to concatenate ranges of text (previously it was painful to do)

• ‘delimiter‘ is what we put between the different words. Here, David is using the CHAR(10) to create a line feed (carriage return). It could be anything we want.
• ‘ignore_empty‘ type in a comma to ignore empty cells. You would normally want to ignore it.
• ‘text1‘ David isn’t concatenating text from a range of cells. He uses MID(A1,{1,4,7,10,13},3) to extract 5 different sets of 3 characters from cell A1. Array constant  {1,4,7,10,13} followed by ,3 extracts characters 1, 2, 3 followed by our ‘delimiter’ line feed, then extracts characters 4, 5, 6 followed by our ‘delimiter’ line feed, etc. MAGIC!

Practice!

I wanted to practice both solutions. Here is my Excel file!  It’s like ice cream and gelato. Both please!

Recap

There is always something new in Excel! A new puzzle, a new tool, a new function!

You might not love Excel puzzles as much as Oz, David and I but increasing your knowledge will help you become more productive and possibly get promoted!

Check out my recommended Excel training, bookmark Oz’s YouTube channel and David’s blog.

About Me

My name is Kevin Lehrbass. I’m a Data Analyst.

I’ve used several different data tools over the years but I’ve always used Microsoft Excel! It’s my favorite!

My dogs Fenton and Cali seem to know when I need a break from data. They start whimpering and demand that I take a break 🙂