Text-to-column with a complication

Hello all! Happy to join the community.

I have the following hard-coded in a single cell:
-2657.83 2015-06-15, 2707.13 2015-08-21, 100.04 2015-08-23

These are essentially:
CASHFLOW1_AMOUNT CASHFLOW1_DATE, CASHFLOW2_AMOUNT CASHFLOW2_DATE, CASHFLOW3_AMOUNT CASHFLOW3_DATE and so on.

My objective is to calculate a XIRR= using these cash flows and dates. Therefore, I need to extract it in a format like: the amounts next to each other in consecutive cells; and then the dates next to each other in consecutive cells. That way, I can then run a XIRR(values,dates).


+ Bonus question: the above is an example for one row of data. I have 50,000 rows and some of them have 3 cash flows, some other 4, etc. Is there a way (doing the above) to automatically align all cash flows and align all dates?


Thank you in advance.

With Power Query, you can manipulate the data as desired.

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.