Sometimes you have formatted reports that you need to transform into a data table in order to get more statistics or make a pivot.

Here you can find the sample file used in this post. You can copy from the Data Template sheet to exercise yourself.

The file was used also for showing how to Filter bold text.

What we have and what we need

We have something like below and we need to add 4 new columns:

1. Date (1): the value in cell B2
2. Other header info (1): the value in cell B3
3. Code (2): the numbers from column A
4. Description (2): the text from column A

We’ll add all the new info to the end of the data.

To start, select all sheet and apply Autofilter.

Step 1. Add the Date and Other header info

This one should be very simple:

1. Select all lines that are not blank. You can use column B here.
2. Put the new columns name on col O and P.
3. Either copy/type the Date and Other header info or use references: =$B$2 for Date and =$B$3 for Other header info. Notices the $ sign – we need an absolute reference.
4. Autofill for all selected lines

Step 2. Separate the Code from Description

1. If the data is filtered, un-filter it
2. Select column A and go to Data=> Text to columns. Here are steps on each screen of the wizard:
   1. Select Delimited
   2. Tick Semicolon
   3. In Destination field type (or fill in by mouse click) =$Q$1. This way you have both the original column and the split data
3. Add names to column Q and R: Code and Description and delete the info in cells Q2 and Q3.
4. Remove and apply again Autofilter in order to have these 2 new columns too.

Step 3. Create Code and Description columns

1. We need to get rid of all blank line so filter again on column C = blank and column Q = blank. Delete the blank lines and un-filter the data.
2. Keep in mind the reference of the first code, here is Q4
3. Filter the data on column B <> blank
4. Go on Code column, Q, and select the first cell on a row lower than 4. Here it will be Q6.
5. In this cell, type the reference of the first Code, Q4
6. We can use Autofill right to add the same formula to Description column, R. Either use mouse or Ctrl+r.
7. Autofill for all lines. Your data will look like this 
8. Once you are happy with it, copy and paste it as values. Then you can delete the extra rows and columns.

In case you need to process multiple files this option is too time consuming. You can use VBA instead, see here how.