Find specific text within larger text string

Question

I’m developing a personal financial budgeting and cash flow application in Excel 2016 on windows 10 and need help in developing a set of formulas that will:

1. Extract specific phrases - one at a time, of course - from within a longer string of text that matches the title of the appropriate line label for that type of transaction.
2. Subtotal all similar transactions based on the matched line label-to-transaction description for related transactions.

I created the “Line Labels” as a means to help tie a bank transaction to the appropriate budget category. The source for my data is a cash flow spreadsheet and downloaded transactions (in XL format) from my financial institution. Here are some criteria that I’ve identified concerning the transaction descriptions from the bank:

1. The desired phrase varies in length according to the type of transaction and what entity the transaction is with.
2. All transactions with a specific entity all contain the exact phrase somewhere within the entire string of text.
3. The identifying string can appear at any place within the description string (almost as if random).
4. Not all transaction descriptions begin with the same characters as in the examples below.
5. Since the first letter of any given phrase could be the first letter of a separate phrase, trying to use FIND to identify and categorize multiple transactions could lead to different transactions being improperly grouped together and subtotaled.

Here are examples of a few transactions with their descriptions and the corresponding line labels I’m trying to match the transactions to. Sensitive data has been deleted and the total length of the two description strings vary.

FORD MTR            Withdrawal ACH FORD MTR CHGO TYPE: ONLINE PMT ID: XXXXXXXXX

BANK OF AMERICA     Withdrawal ACH BANK OF AMERICA TYPE: ONLINE PMT ID: XXXXXXXXX

INFINITE ELECTRIC   Online Payment XXXXXXXXXX To INFINITE ELECTRIC

If I can find a way to bind similar transactions to their proper line labels, I believe I can use a form of SUMIF or similar function to subtotal like transactions for each line label in the budget worksheet. I think I’ve given enough info but…. If more is needed, please let me know. Thank you very much in advance.

Answer 1

Perhaps the most elegant solution is to write a VBA function that leverages Regular Expression pattern matching, so you could pass the value of the line label and the return the type of transaction or other relevant details. If you are not comfortable with that approach, or prefer to use Excel formulas, then you could do so by setting up columns for each type of transaction you want to parse (Withdrawals, Payments, Deposits, Transfers, etc.). Then you'll have to determine what data you need to parse for each. Then you can use Excel's Text formulas (SEARCH, FIND, MID, etc.) to parse out what you want.

For example, if you wanted to parse the organization/entity name from a Withdrawal type transaction, assuming the entity always follows the word 'Withdrawal' within the line label, your formula would be (line label in A10):

=IF(ISERR(SEARCH("Withdrawal",A10,1)),"",MID(A10,SEARCH("Withdrawal",A10,1)+LEN("Withdrawal")+1,FIND(":",A10,1)-LEN("Withdrawal")-2))

Similar formulas could be used to parse the Payment ID, or other types of line labels.