Split one column into two based on value

evfred

New Member
Joined
Jun 23, 2014
Messages
5
Hi all. I have been downloading bank statements as a csv file to excel. Everything was ok when they had separate columns for debits and credits. I would sort the data, copy it and paste it into my main programme.
Now they changed it so all debits and credits are in the same column. To make matters worse they have the debits as negative numbers.
What I want to do is separate the column into two columns Debit and Credit, then convert the negative numbers to positive. I need the new figures to be in the same row as the original row because of the details of the transaction need to line up with the amount. It would need to be a vba formula I assume.
Thank you in advance for any advice.
Regards Evfred
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,202
Office Version
  1. 365
Platform
  1. Windows
Does this do what you need ?
If you make it a table and you drop the data into it, it is pretty low maintenance.

How does the download come ? If you are getting a csv file or something similar, Power Query would be an easy option.

Book2
ABCDEF
1DateDescriptionAmountDebitCredit
215/07/2021Line 1-100100 
315/07/2021Line 250 50
415/07/2021Line 3200 200
515/07/2021Line 4-2020 
615/07/2021Line 5-100100 
715/07/2021Line 650 50
815/07/2021Line 7200 200
915/07/2021Line 8-2020 
10
Sheet1
Cell Formulas
RangeFormula
D2:D9D2=IF(C2<0,-C2,"")
E2:E9E2=IF(C2>0,C2,"")
 
Solution

evfred

New Member
Joined
Jun 23, 2014
Messages
5
Does this do what you need ?
If you make it a table and you drop the data into it, it is pretty low maintenance.

How does the download come ? If you are getting a csv file or something similar, Power Query would be an easy option.

Book2
ABCDEF
1DateDescriptionAmountDebitCredit
215/07/2021Line 1-100100 
315/07/2021Line 250 50
415/07/2021Line 3200 200
515/07/2021Line 4-2020 
615/07/2021Line 5-100100 
715/07/2021Line 650 50
815/07/2021Line 7200 200
915/07/2021Line 8-2020 
10
Sheet1
Cell Formulas
RangeFormula
D2:D9D2=IF(C2<0,-C2,"")
E2:E9E2=IF(C2>0,C2,"")
Hi Alex Yes that works thank you
 

Forum statistics

Threads
1,137,060
Messages
5,679,372
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top