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

- Don’t suggest a manual method which requires reasonable labor.. That’s a NO NO!
- 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.

- CHAR(10) is used to specify a line break
- 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

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

**Step 3)** Here comes the Formula

Don’t freak out.. Let me explain

- I am searching for the starting point of the first serial number (
) and**1.** - Then the starting point of second serial number (
**2.**) - The position of these 2 will give me how much text to extract
- I drag the formula across the cells and boom!
- 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**

## Try Other Excel Challenges..

**Find the Shift Timing Challenge****Solve the Question without using Logical Functions****Re-arrange data for the Pivot Table**