Using Basic Excel Functions
 

I've got a string of characters (city, state and zip) that is all
contained in 1 cell. I know how to separate them into different cells
using basic excel functions; i.e.,

Assuming data is in A27:
City
=Left(A27,Len(A27)-8)

State
=Mid(A27,Len(A27)-7,2)

Zip
=Right(A27,5)

The problem is that the character strings are in different rows; 1 set
of data is in Row 27 and the next is in Row 41. How do I increment the
rows by 14 using the formulas above?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Using Basic Excel Functions
 

=LEFT(OFFSET($A$27,(ROW($A1)-1)*14,0),LEN(OFFSET($A$27,(ROW($A1)-1)*14,0))-8
)

=Mid(OFFSET($A$27,(ROW($A1)-1)*14,0),LEN(OFFSET($A$27,(ROW($A1)-1)*14,0))-7,
2)

=Right(OFFSET($A$27,(ROW($A1)-1)*14,0),5)

Then drag fill down the column.

assumes data is offset by 14 rows.

--
Regards,
Tom Ogilvy


Novice wrote in message
...

I've got a string of characters (city, state and zip) that is all
contained in 1 cell. I know how to separate them into different cells
using basic excel functions; i.e.,

Assuming data is in A27:
City
=Left(A27,Len(A27)-8)

State
=Mid(A27,Len(A27)-7,2)

Zip
=Right(A27,5)

The problem is that the character strings are in different rows; 1 set
of data is in Row 27 and the next is in Row 41. How do I increment the
rows by 14 using the formulas above?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/