Separate the values in Columns – [Excel Challenge]

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

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

 

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 🙂

DOWNLOAD THE EXCEL FILE HERE

 

SOLUTION TO THE PROBLEM

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)

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

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

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

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)

 

DOWNLOAD THE SOLUTION FILE HERE FROM DOWN BELOW 

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