We're working with small data sets (<3000 transactions) of bank transactions for business clients.
Each business bank has their own set of descriptions as to how they list transactions, and we've identified these transactions for the bigger business banks in our area.
There can be as many as a dozen or so phrases that we would look for to replace or remove in cells using Excel's search and replace feature (CTL +H).
It turns out that nigh half of these phrases are being removed by making the selection to replace them with nothing (empty lines).
I'll select the column, and they repeatedly do the (CTL +H).
So, it turns out that we're this doing repetitive task over and over: search and replace the one phrase and replacing it with nothing, then searching for the next phrase and again replacing with nothing, so on and so on until we replaced or removed all the phrases that we want to.
I've seen some solutions on the Internet of how to do this with the Vlookup function, however, the replacement result is being put in another part of the spreadsheet. And, what I'd like to have is to replace the phrases in their place.
So, for example, I'd like to take the following phrases:
Fsi~*
Sq ~*
Tst~*
IC~*
Orig CO Name:
Card Purchase ??/??
Card Purchase ??/??
Card Purchase With Pin ??/??
Recurring Card Purchase ??/??
Card Purchase W/Cash ??/??
Quickpay With Zelle Payment To
Zelle payment to
and have them removed, by a one-time multiple search for the different phrases, and then replacing them one time with the empty field using Excel's search and replace feature.
I was wondering if power query would be a possible solution, but, when we receive the data set, and normally has a year's worth of bank transactions contained therein, and the only data cleaning that we find ourselves having to do is the search and replace process.
Bookmarks