Macro to separate records and convert one row into multiple columns

[Des888]

Hi, Can you please help?, I need a macro for excel 2007
I have a spreadsheet that has only one row and about 850 columns containing names job titles and a further 9 columns of details)

I need to convert this:
name_1 jobtitle_1 email_1 phonenumber_1 fax_1 example _1 name_2 etc


to this:
one column for names one column for job titles one column for emails etc

Hope this makes sense. Thank you!

 

[Des888]

Thanks, I recorded a Macro just to create it then I clicked on the Macro to edit it and pasted your text in, but it seemed to delete the Macro like it no longer existed and when I went to quit the document it showed the Compile error.

 

[SteveO59L]

You are using excel ?

 

[Des888]

Yes, using 2007, I am used to the older versions of excel but I clicked on the developer tag and far right clicked on Macros, highlighted the Macro, clicked edit and pasted into this. Do you thing I have done this incorrectly?

 

[SteveO59L]

Nope. Sounds ok to me.

Send a pm

 

[EcomTom]

Can you post exactly how you see the data in one cell? This way I can test it out.

 

[Des888]

I will get back tomorrow if okay, I need to get this sorted, maybe I can figure a way to show the spreadsheet on this site as I rarely ever do forums, thanks again!

Firstname lastname Job title line 1 Direct†line† 020 8888 8888 Fistname last name @domain-account.co.uk Fax 020 8777 7777 Next First name Next Job Title NextDirect†line† 020 8877 7777 NextFirst.name @domain-account.co.uk NextFax 020 8333 3333

Just noticed some unusual characters † creeping in between the Direct Line text?

I am wondering if the @domain-account.co.uk is playing havoc with it too as I could only edit this in this document.