Separate the values in Columns - [Excel Challenge] 1

Here is a fun challenge for this weekend. Recently I was asked a question that if I could separate the data from a single cell in different columns?

Sure enough I did that but I wanted to figure out if there was an even smarter way to do that


What is the Challenge.. ?

Take a look at this data

Separate the values in Columns - [Excel Challenge] 2

In the first column we have the names and in the second column we have multiple certification programs done by the person. The challenge is to separate each certification in a single cell

Separate the values in Columns - [Excel Challenge] 3


Quick Rules..

  1. Don’t suggest a manual method which requires reasonable labor.. That’s a NO NO!
  2. You are free to solve this by a VBA code, a formula or even any trick that can do the task


Get Cracking..

Take a shot at doing this in the best possible manner and post your answers in the comments below. I will reply to all comments 🙂




Posting it on 28 July 2016

Now if you have tried your hand on the problem, you would have noticed that the certifications are divided by a line break (that means each certification point is a different line in the same cell)

Separate the values in Columns - [Excel Challenge] Solution 1

Step 1) Is to substitute line breaks with a Space Bar

Separate the values in Columns - [Excel Challenge] Solution 2

A Simple Substitute formula can do that.

  1. CHAR(10) is used to specify a line break
  2. Now we wont be having the line breaks in the text


Step 2) Create a Dummy – Write Numbers as Text on the Top

Separate the values in Columns - [Excel Challenge] Solution 3

Take a note of the few things

  1. I have written numbers as text. To do that just use an apostrophe in the beginning
  2. Also after the number there is a dot ‘. and a space in the end
  3. This pattern will help me identify serial numbers of certifications


Step 3) Here comes the Formula

Separate the values in Columns - [Excel Challenge] Solution 4

Don’t freak out.. Let me explain

  1. I am searching for the starting point of the first serial number (1. ) and
  2. Then the starting point of second serial number (2. )
  3. The position of these 2 will give me how much text to extract
  4. I drag the formula across the cells and boom!
  5. Its done!


Looking for help on learning formulas ? 100 Excel Formula Videos

There is one drawback though – The Excel file for some reason is getting usually large (10Mb+). And I have no reasonable answer for that 🙁 (May be because of the line breaks in the data)




Try Other Excel Challenges..

  1. Find the Shift Timing Challenge
  2. Solve the Question without using Logical Functions
  3. Re-arrange data for the Pivot Table


Topics that I write about...

Download Smart Ebooks on
Excel and Power BI