=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/