Copy or split cell value up to a certain word in the cell.

[KateD]

Hello,
I have spent a lot of time museing over my problem and i have looked at a number of questions which are similar to mine. However, none have answered it and I do wonder if I am going about this the wrong way.
I am a teacher and I use google agenda to plan my lessons. Once the course is complete i create a report of my agenda using GTimeReport. it looks like this ( What i want to achieve is to use this form as mail merge data to import what i have written to a specific word doc we have to use.)

column K is what i am struggling with. each text you see in K needs to be a column header. The text that would be below each of these is where i write my details, and these details would be therefore under each column header.
I have so far merged the cells into one, thinking i could then split the cells when it arrives at each "header" and then take this data into a new column.

1. I cant find a code ( or create one) that will do this
2. move this data into a column. ( i think i could do this once the data is split)
3. is this even the right way to go ????????

thankyou in advance for your help.

 

[sijpie]

Hi KatD, This should be a very simple thing to do. However, merging into one cell is the wrong way. (You can do it that way, but it is a more difficult route).
A simple way is to copy the column K (not merged) and then paste it transposed (that is one of the options when pasting). You would then have all the headings, but with blank columns between, which you could remove.

Another way is to load column K into an array, using vba, and then in vba copy each cell of the array that is not empty into an output array and drop this into the sheet, voila your headers are there.

I can help you with these macros if you wish.

 

[KateD]

Sorry it gas taken a while to respond. Thank you for the feedback. I will have a try at what you have suggested and will come back if I need any help.