Find Replace

[GB]

Hi,

I have my bank records downloaded to excel and I want to remove some of the noise from my "transaction description" column and place this noise into a separate column, so I can sort and categorise my records in a good way.


For example many records start with "ATM#245367-" or "ATM#854910-" which preceed the actual expense in the transaction description column. This makes sorting difficult because of the numeric portion.


The following are the complete list of items I want to remove from the transaction description column along with the number of characters starting from the left in the string. These are...


Start With, Char

ATM#, 11

BPAY Net, 22

Direct, 13

POS#, 11

VISA-, 5


My challenge is finding these records and removing from the transaction description (column G) field then placing into a new column (column F).


Any help would be appreciated.

regards

GB

 

[keymaster]

Interesting question GB. I do this often for my bank statements too. But never used formulas as my statement is even more complicated. But in your case, it makes good sense.


You can use SUMPRODUCT, LEFT, LEN etc. to check which "Start with" matches with a given transaction and replace the first "n" characters.


Assuming you have start with, char table in G3:H7, and also assuming we have running numbers 1..x in adjacent column - F3:F7.


Now, if your transaction is in B3, in C3 you can write

=SUMPRODUCT((LEFT(B3,LEN($G$3:$G$7))=$G$3:$G$7)*($F$3:$F$7))

This finds the match of start with from the list in F3:H7.


How it works?


Lets say your transaction is ATM#245367-Withdraw for shopping.


Now, the LEFT(B3,LEN($G$3:$G$7)) portion evaluates to

={ATM#, ATM#2453, ATM#24, ATM#, ATM#2}


This will be matched with the list of start withs you have and you will get

={TRUE, FALSE, FALSE, FALSE, FALSE}


We just multiply this with running numbers {1,2,3,4,5} to get corresponding start with number. Result will be 1.


Note: Result will be 0 if there is no match.


Next, you can use a formula like this to replace first few characters:


=IF(C3=0,B3,MID(B3,INDEX($H$3:$H$7,C3)+1,LEN(B3)))


If the result is 0, we just show entire text, else, show middle portion starting with 12 character (in this example).


Please download a file with this formulas to understand better.


http://img.chandoo.org/playground/find-and-replace-gb.xlsx

 

[GB]

Thanks Chandoo, you are a gentleman. Thanks for taking the time to answer. Elegant solution.

cheers

GB

 

[Krishnakumar]

Hi


I think this should also work,


=IFERROR(MID(B3,LOOKUP(9.999E+307,SEARCH($G$3:$G$7,B3),$H$3:$H$7)+1,255),B3)


Kris