Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual basic functions | Excel Worksheet Functions | |||
Excel n00b, how to do basic math functions | Excel Worksheet Functions | |||
Basic Excel formula | Excel Worksheet Functions | |||
Basic excel functions | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |