I've been given an excel spreadsheet with useful names, numbers and emails for work, but the genius who created it put the office name in A1, telephone number in A2, contact name in A3, title in A4 etc

Is there an easy way to move all the different info into different columns so i end up with a line for each contact name, under the individual offices?

I tried a simple drag,and copy thinking I could then delete the extra lines, but they don't all have the same number of lines, so i guess I'm looking for something to say if A6 begins 01, copy to D2 or similar, then I can use the same formula for 07, titles, etc?

 

Can you not select all the data then sort it by the office field A1?

 

No, because every contact has all their info in a single cell.

 

Ahh, with you now! Bah

 

Sorry, I meant single column

 

I just tried something with one of my spreadsheets......

Saved the file as a text.csv

then opened it, but as it's a txt.csv you get to make a few selections as to how the data should be imported, you can select an option and it will show you a small preview of what the data will look like. All my data ended up in A1, A2, A3 etc

This is using Excel 2007

 

Sorry fella, I misread the original post.

I'd be trying to write a macro to do it. I'm sure it will be possible

 

If I understand, text to columns is your friend. In the data menu.

 

you can use the MID function to extract data or use the text to column feature

with mid

if you have 1 cell (lets say z67) which has " a1 spiky loves sheep"

you can do "=mid(z67,1,2) this will start at charachter 1 and extract 2 characters for and would give you "a1"

or you could do =mid(z67,4,5) this would give you spiky

 

Ive done stuff like this, exporting to csv, modifying that file maybe with search/replace (notepad/wordpad) then reimporting back into excel,

prolly not what ur after, but I didnt read your post properly either - too late

 

email me the file if you want me to sort

wont take long

 

i've pm'd him offering this too; i expect you're better than me tho; its been a while

 

Cheers fellas, especially for those offering to do the job for me, but it's done now.

I found that COPY, PASTE SPECIAL, TRANSPOSE was able to transfer the cells from Rows to Columns, but I had to highlight the relevant cells for each record because they all had different numbers of Rows in them, and I had over 100 records so it took a while!!

 

That was my first suggestion, I edited it because I thought you wanted to re-order the cells too!

Glad you're sorted