Home » Questions » Computers [ Ask a new question ]

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: 64
Total answers/comments: 2
Guest [Entry]

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


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. :)"