I have been given a .rtf file with thousands of customer records that my boss wants transferred to excel. The data is not in a table, it's a strange mix of text boxes and drawn lines.
I can copy and paste to Excel but all the data is pasted into a single column and i need to separate the data into a single row for each customer. There is some uniformity, each customer has five cells each and the customers are separated by a blank cell. My sheet looks like this:
a1. Customer 1 account no
a2. customer 1 name
a3. customer 1 address
a4. customer 1 post code
a5. customer 1 notes
a6. blank cell
a7. Customer 2 account no
a8. customer 2 name
I can manually select cells A1 to A5 and then copy/transpose to cell B1 then keep going for each customer but that will take a long time with the amount of data.
Is there some way I can do this with VBA? E.g. is it possible to remove the blank cells then incrementally copy/transpose blocks of five cells for each customer into separate rows until there is no more data in column A?
We are using Excel 2010 at work.
Hope you can help as this is way beyond my skill level!