Move a Value Based on Text

t2true

New Member
Joined
Dec 2, 2015
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
I've copied data from a bank statement and pasted into Excel. But, the credit and debit values paste in the same [Debit] column; they need to be in two separate columns. Fortunately, each transaction description contains either the word "credit" or the word "debit".

Column C = Description
Column E = Debit
Column F = Credit

Is there a formula to search for the word "credit" in the description column and, if true, move the value in the debit column over to the credit column in the same row? If a VBA script is the answer, please provide detailed instructions. I do not know VBA at all.

Thanks
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You could use VBA, but it really isn't necessary. You could just add columns to your current data, and add formulas, and copy them down.
If you can post a sample of what your current data structure and actual data looks like, we can help you with that.
 
Upvote 0
Thanks for the feedback. I'm sure there is a formula, but I don't know what it is.
Below is the data structure: Need to move the credit values from the debit column into the credit column.

DateDescriptionTrans #Debit (-)Credit (+)
4/8/2019​
Debit Wire Transfer
0​
$100.00​
4/1/2019​
Debit Fee
0​
$15.00​
4/1/2019​
Credit Incoming WIRE TRANSFER
0​
$500.00​
2/8/2019​
Debit Wire Transfer
0​
$800.00​
2/6/2019​
Debit Fee
0​
$15.00​
2/6/2019​
Credit Incoming WIRE TRANSFER
0​
$500.00​
1/9/2019​
Debit Wire Transfer
0​
$300.00​
1/4/2019​
Debit Fee
0​
$15.00​
1/4/2019​
Credit Incoming WIRE TRANSFER
0​
$900.00​
6/7/2019​
Debit Wire Transfer
0​
$200.00​
6/4/2019​
Debit Fee
0​
$15.00​
 
Upvote 0
OK, assuming that your original data above is in the range A1:F12, the way to do it without VBA would be to hide the Debit and Credit columns (columns D and E), and insert two new ones (columns F and G), and place the following formulas in the following cells and copy down for all rows:
in cell F2: =IF(LEFT($B2,3)="Deb",$D2,0)
in cell G2: =IF(LEFT($B2,3)="Cre",$D2,0)
 
Upvote 0
If you would like a VBA option to move the values within the current fields, you can use this code:
VBA Code:
Sub MoveData()

    Dim lRow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B
    lRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows
    For r = 2 To lRow
        Select Case Left(Cells(r, "B"), 3)
            Case "Deb"
                'Do nothing, fine as is
            Case "Cre"
                Cells(r, "E").Value = Cells(r, "D").Value
                Cells(r, "D").Value = 0
            Case Else
                'Do nothing
        End Select
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Move completed!"
    
End Sub
 
Upvote 0
That's great stuff Joe!! Thanks for the help. The formula worked great. I will give the VBA a try as well.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top