Parsing data - variable length cells

K

Karen

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 !
 
T

Trevor Shuttleworth

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
 
M

mudraker

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top