Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing data - variable length cells
I'd like to parse data like this from a single cell (Treasury bond
interest payment information). 6/30/2004 5625 0 12/31/2004 5625 0 6/30/2005 5625 1000000 The format is always the same, date space interest payment space principal payment space. So on the last payment date, there is a value for the principal instead of just zero. Depending on the time until the bond's maturity, this string set is of varying lengths. Is there a way to separate each date & add the 2 corresponding payments in individual cells, allowing for each interest payment & principal payment to be added together in one cell? Result: A1 B1 C1 D1 E1 F1 6/30/04 5625 12/31/04 5625 6/30/05 1005625 Thanks ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing data - variable length cells
Karen
look at Data | Text to Columns | Delimited (by space) to get the data in separate cells on the same row. Then a simple formula will add the two values together. Regards Trevor "Karen" wrote in message om... I'd like to parse data like this from a single cell (Treasury bond interest payment information). 6/30/2004 5625 0 12/31/2004 5625 0 6/30/2005 5625 1000000 The format is always the same, date space interest payment space principal payment space. So on the last payment date, there is a value for the principal instead of just zero. Depending on the time until the bond's maturity, this string set is of varying lengths. Is there a way to separate each date & add the 2 corresponding payments in individual cells, allowing for each interest payment & principal payment to be added together in one cell? Result: A1 B1 C1 D1 E1 F1 6/30/04 5625 12/31/04 5625 6/30/05 1005625 Thanks ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing data - variable length cells
2 ways
1/ Parse data (Text To Column) using space as field devider then use a formula to add the various column data together to give the desired string result 2/ build a macro or a function to do the same as option 1 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
charting variable length line data | Charts and Charting in Excel | |||
parsing data with no set delimiter or fixed length. | Excel Discussion (Misc queries) | |||
Inputting variable length data - outputting fixed character result | Excel Discussion (Misc queries) | |||
Parsing Last Character in variable length text field | Excel Worksheet Functions | |||
Graph with variable data length | Excel Discussion (Misc queries) |