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.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc)", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc)", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).1", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).2", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).3", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).4", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).5", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).6", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).7", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).1", type number}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).2", type date}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).3", type number}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).4", type date}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).5", type number}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).6", type date}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).7", type number}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).8", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).1", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).3", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).5", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).7", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).2", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).4", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).6", "Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).8"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).1", "Cash Flow1"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).3", "Cash Flow2"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).5", "Cash Flow3"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).7", "Cash Flow4"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).2", "Date1"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).4", "Date2"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).6", "Date3"}, {"Cash flow and dates (CASHFLOW1_AMOUNT CASHFLOW1_DATE, etc).8", "Date4"}})
in
    #"Renamed Columns"

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.