Separate Text

[Abdullah95]

This is a bank account statement i got it in Text format in Notes. i want to separate the text and Dr and Cr so i can reconcile it, how can i do it.

 

[AlanSidman]

Pictures are worthless. We cannot manipulate data in a picture. Suggest you upload your sample file as an attachment. Power Query may be the answer you need, but we need a file to test. Also, it would be very helpful if you mocked up what you want your solution to look.

 

[Peter Bartholomew]

@AlanSidman
Try this. I transferred some of the data as a picture.
I have attempted solutions both by PowerQuery and Lambda functions.
I wouldn't say either is an object of beauty.

= LET(
    transactions, FilterTransactionλ(import),
    transactAmt,  MAP(transactions, ExtractAmountλ),
    amounts,      DisplyasColumnsλ(transactAmt),
    HSTACK(DATEVALUE(LEFT(transactions, 7)), amounts)
  )

 

[Abdullah95]

@AlanSidman
Try this. I transferred some of the data as a picture.
I have attempted solutions both by PowerQuery and Lambda functions.
I wouldn't say either is an object of beauty.
View attachment 85476

= LET(
    transactions, FilterTransactionλ(import),
    transactAmt,  MAP(transactions, ExtractAmountλ),
    amounts,      DisplyasColumnsλ(transactAmt),
    HSTACK(DATEVALUE(LEFT(transactions, 7)), amounts)
  )

Hi i did not get it how to use the function. here can you share how

 

[Peter Bartholomew]

Do you use Excel 365? If not, open the PQ editor and focus upon the Power Query solution.

 

[AlanSidman]

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.