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
- 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
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 ‘.‘ and a space in the end
- 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 (1. ) and
- 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)
Try Other Excel Challenges..
- Find the Shift Timing Challenge
- Solve the Question without using Logical Functions
- Re-arrange data for the Pivot Table