Simple (I hope) Excel question about

Simple (I hope) Excel question about

I am doing a directory for my neighborhood. We had most of the information from a previous directory. The information was entered: A1 name, B1 address and C1 phone number; B1 name, B2 address, C2 phone number etc. The publisher wants the information in a different format A1 name, A2 address, A3 phone number, A4 blank; A5 name, A6 address, A7 phone number, A8 blank etc... Is there an easy (or heck - a not so easy) way to have Excel change the format of the information without me having to hand type 1300 households information?

Asked by: Guest | Views: 210

Total answers/comments: 2

Comments display order:

Guest [Entry]

Guest

Guest [Entry]

"The first one - converting a data table to a single column with blank rows separating each record - I'd actually do in Word. Copy & paste the data into Word, convert table to text using Tab as the column separator, replace each existing paragraph mark with two paragraph marks (^p with ^p^p), replace each tab with a paragraph mark (^t with ^p), and copy and paste the result back into Excel.

For the second task, if the addresses are formatted the usual US way - 123 Main Street - then you'll need to use Excel's text manipulation functions to split out the address number from the street name. If the addresses are in column B (and there is always a single space separating the number from the street), then in D1, put

=Trim(Left(B1,Find("" "",B2)))

and in E1, put

=Trim(Right(B1,Len(B1)-Len(D1)))

and fill down. Then, as before, use Word to strategically replace tabs with paragraph marks to get the arrangement you need.

If it's stupid but it works, it's not stupid. :)"