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 !

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 !

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/