Formula to structure messy CSV export and extract key information

[downcrusher]

Hi all,
For some reason my bank's csv export is quite messy. I have tried to create a formula to extract the most important transactions related information (such as, transaction date, transaction counterparty and transaction amount).

It works for the majority of the transactions. However, some transactions are reported slightly different and the formula fails. The idea is to store this document online and update it via excel online in the most convenient way possible.

I attached a file with examples. The first two rows work perfectly, the subsequent rows don't work.

Perhaps someone has a solution to this problem.

Thanks in advance!

[pjwhitfield]

Based on the sample you've supplied, text to Columns seems to work perfectly. Apologies if I'm missing something

[José Augusto]

Hi

Have you tried using [Text to columns] on the Data tab?

Is your original data in a single column or in multiple columns?

[downcrusher]

Based on the sample you've supplied, text to Columns seems to work perfectly. Apologies if I'm missing something

Apologies - I should have been more clear:

Date is correct across all columns. The other variables are incorrect.

Row 7:
- Counterparty should be "BANKXXX"
- Account number should be "IBANYYY"
- Amount should be 25,068.80

Row 8:
- No Counterparty mentioned ("")
- Account number 61150020
- Amount should be 168.00

Row 9:
- No Counterparty mentioned ("")
- Account number IBANYYY
- Amount should be 16,612.06

Row 10:
- No Counterparty mentioned ("")
- Account number not mentioned ("")
- Amount should be 16,427.31



Also, I want to avoid any manual manipulation (such as text to columns), as I want to make it as efficient as possible.

[pjwhitfield]

Would you be willing to upload one of the original CSV files? It's quite hard to establish what exactly happens from an amended version?

You could go into the file in Notepad etc and amend the personal info beforehand.

[José Augusto]

Try this

In H12 concatenate data

Please Login or Register  to view this content.

In K12 use

Please Login or Register  to view this content.

In L12:Z17 (L12:AA17) use

Please Login or Register  to view this content.

See the file

[José Augusto]

Hi
To adjust the fields of the two groups (first group has eleven fields and the other seventeen) you can use the following approximation
In I12 place the concatenation of your data

Please Login or Register  to view this content.

in H12 use the following formula

Please Login or Register  to view this content.

All other formulas remain.
See the file