Expert: Tom Ogilvy - 8/9/2008

Question
Hi Tom,

I thought I might see if you can help me with this one. I have a list of addresses that I copied from a word doc. I pasted them into excel and want to put them in columns.

Example

Currently:

A1 = Name
A2 = Street Address
A3 = City, State ( also want to remove comma and separate into 2 columns
A4 = Tel #
A5 = Blank
A6 = Name.... and so on

Want to format so that I have:
  Name  Address  City State
A1
A2 Etc

The problem is that I have found formulas that don't work. I want to end up with a complete list of data removing the rows I don't need.

I would appreciate any help.

cheers,

rob.

Answer
Rob Thomas,

So every address is 4 rows followed by one blank row.

in B1 put in

=Indirect("A"&(row()-1)*5+1

in C1 put in
=INDIRECT("A"&(ROW()-1)*5+2)

in D1 put in
=LEFT(INDIRECT("A"&(ROW()-1)*5+3),FIND(",",INDIRECT("A"&(ROW()-1)*5+3))-1)

in E1 put in
=TRIM(MID(INDIRECT("A"&(ROW()-1)*5+3),FIND(",",INDIRECT("A"&(ROW()-1)*5+3))+1,255))

in F1 put in
NDIRECT("A"&(ROW()-1)*5+4)

then select B1:F1 and drag fill down the columns until you run out of data.

--
Regards,
Tom Ogilvy