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 🙂

## 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