Expert: Tom Ogilvy - 8/11/2008

Question
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




---------- FOLLOW-UP ----------

QUESTION: Hi Tom,

I have to say you are pretty brilliant and I am almost there! The only thing is that the name is still missing. I now have my list running down column A, and the list of addresses, city and state running consecutively down columns c-e but... the name (as in A1, A6) is missing. Is this supposed to appear in column b?

Answer

Rob,

It looks like  when I pasted in the formula for column B from my worksheet where I tested it (successfully), the last parenthesis was left off

try changing

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

to

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

the formula in B1 and C1 would should be identical except for the +1 in column B and the +2 in column C.

--
Regards,
Tom Ogilvy