Multiline Narration in Single line

[Amit_KK]

Hello Experts,
I Have an issue extraction the narration from bank statement,

The statement is too large in text format and I have imported it in excel. The problem sample file is attached for reference. Please refer.

Also please suggest if there are any tips to import text statements in proper way in excel.

Thanks.

 

[Marc L]

Hi,​

​

maybe a bad import or a bad file, who knows without any source file ?!​

​

If the source file was not created by Dumb or Dumber, that just needs to well answer to the Import Assistant …​

​

 

[Amit_KK]

No No.. it is extracted from text file.. and it is formatted in such a fashion only..

The part narration in same column in new line in text file.. No error on extracting the data.

I not able to attach the sample text file here.

 

[GraH - Guido]

Hi, bank statements can be a mess. For me the easiest way to solve this is using Power Query.
I made a mess of he applied steps, but just to show a possible result, I've uploaded the file.

To apply the same (given you have Excel 2010 or higher and are working on Windows)

1. Load data from range/table
2. Add Index
3. Add Custom Conditional Column: if data is empty then null else Index
4. Fill Down Custom and Date
5. Group By on Date and Custom, using All Rows as aggregation. Name this column "All".
6. Add Custom column and type this formula: Table.Column ([All], "Description")
7. Expand this list and use extract values with delimiter of choice.
8. Close and Load to Excel

Most of the steps are done by using mouse operations on the UI. So it's rather user friendly.

 

[Amit_KK]

Thank You Sir,
Actually I am not a frequent user of power query,

but you have guided in detail .. I'll definitely try out.

Thanks.

 

[Marc L]

No No.. it is extracted from text file.. and it is formatted in such a fashion only..

That's exactly what I wrote : a bad import or a bad file, who knows without any source text file …​

​

 

[bosco_yip]

I think Power Query is the better choice and herein formula solution just for your reference only.

1] In E2, formula copied down :
=IFERROR(AGGREGATE(15,6,Table1[Date],ROW(A1)),"")

2] In F2, formula copied down :
=IF(E2="","",TEXTJOIN(" ",1,INDEX(Table1[[#All],[Description]],AGGREGATE(15,6,ROW(Table1[Date])/(Table1[Date]<>""),ROW(A1))):INDEX(Table1[[#All],[Description]],IFERROR(AGGREGATE(15,6,ROW(Table1[Date])/(Table1[Date]<>""),ROW(A1)+1)-1,MATCH("zzz",C:C)))))

N.B. : TEXTJOIN is a new function available in Office 365 and Excel 2019 only

 

[Peter Bartholomew]

Another formula solution


With future versions of Office 365, the spreadsheet solution looks even more like a snippet of computer programming



[Note: The LET formula requires Office 365 insider channel and can be deleted]

 

[herofox]

thanks alot bosco_yip
Amit_KK
if you haven't Textjoin Function in your Excel.... you can Insert New Module and Put This Code For This Function

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function

 

[Peter Bartholomew]

@herofox
Another function that would be of general value is ACCUMULATE(range) that runs through a range summing as it goes. Here
= ACCUMULATE( SIGN(Source[Date] ) )
to group the source data records. but, more generally, used to calculate running balances from flows.

I have a version written by Charles Williams written in C and with a few more parameters, but a VBA version would be quite usable.
Note: the other parameters provide an initial value. add a percentage change, and specify opening or closing balances.

 

[Amit_KK]

thanks alot bosco_yip
Amit_KK
if you haven't Textjoin Function in your Excel.... you can Insert New Module and Put This Code For This Function

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function

Sorry Sir,
But getting #Name error in code.. formula function not pooping up while typing.

 

[Amit_KK]

Another formula solution
View attachment 68005

With future versions of Office 365, the spreadsheet solution looks even more like a snippet of computer programming

View attachment 68007

[Note: The LET formula requires Office 365 insider channel and can be deleted]

Thank You!!

 

[herofox]

Amit_KK
i didn't upload this Function to you before test it and it work as well !!

 

[Amit_KK]

Amit_KK
i didn't upload this Function to you before test it and it work as well !!

Ok.. probably the error from my side then ...

I have pasted the code in VB editor and that's it .. tried to write it in sheet .. is that anything more to do ? That I have not ?

Please inform.. thanks

 

[Peter Bartholomew]

@Amit_KK
I do not think it is you. As far as I can see, the function is written to concatenate text from a range, or even multiple ranges. In this case, I think the argument passed to the function is an array and not a range so an alternative loop structure is needed to work over the elements of an array.
@herofox

 

[Peter Bartholomew]

@herofox
I wrote a version of the formula that does return a range for each TEXTJOIN.

= LET(
start, XLOOKUP( [@Record], Source[Record], Source[Description], , , 1 ),
end, XLOOKUP( [@Record], Source[Record], Source[Description], , , -1 ),
concat, IF( ISREF(start:end), TEXTJOIN( " ", TRUE, start:end ), "" ),
concat )

The trouble is, by the time I have used LET and XLOOKUP, TEXTJOIN is the least of the problems.
What strikes me most of all, is that the result looks nothing like a spreadsheet formula!