Automating monthly spending

Yes, you can search a longer string to see if it contains a shorter string. The following formula will search the description in cell B2 for the shop name found in cell Shops::A2. If the result is TRUE, it contains the shop name.

=COUNTMATCHES(B2, Shops::A2)>0

The problem I am seeing is you have to do that for each and every shop name individually. Either you make one extremely long formula that does each and every check or you do them in separate columns. I imagine your list of shop names will reach into the hundreds. I am trying to figure out how else you can do it so it takes a reasonable number of columns or a reasonable formula no matter how many shop names you have. So far I am not seeing how. Hopefully it is just me being dense and someone else has an idea.

Note: In the formula above, you would probably want to tack spaces onto the shop names to help ensure they don't match another set of the same characters in the description. For example, if your shop name was "Chase" and you search for "Chase" you will get a match on the word "purchase". If you instead search for " Chase " it needs a space on each side to be a match.

=COUNTMATCHES(B2, " "&Shops::A2&" ")>0

I'm not sure if there is a robust method (i.e., one that works all or most of the time) to extract the business name. I was unable to discern a pattern in the spacing/spaces that was common to all rows. For the few examples provided, I was able to extract them to what you see below in a few steps but this is probably sketchy. It may work on the examples provided but probably not in general. Also, I don't yet have a way to get it further. I could strip off the last "word" if there is more than one word, but it will mess up "Tracy Hannson".

HM.COM

Mc Donalds Copenhagen

amazon.com

Tesco Euston

AMZNMktplace amazo

APPLE.COM/BILL

PAYPAL *INTERNETSTO

transfer

Tracy Hansson

TESCO EUSTON

Tesco Euston

McDonalds Copenhagen

Mc DONALDS Copenhagen

1. For strings that have a comma as the 26th character, strip off the first 28 characters and the last 10. Leave the other strings alone.
2. Using the results from (1), any strings do not start with a space, strip off the first "word", including any dots or dashes. Leave the other strings alone
3. Remove all digits 0-9 and get rid of all extra spaces (TRIM)

Part of the problem is there is no solid pattern to what the results should be. Some two "word" names need the second "word" removed while others need the second word to remain. There is no way for a formula to know which is which.

From the looks of it, it has "tab stops" every 10 characters. 10 spaces = a tab. If a field ends before a "tab stop", it pads it with one or more spaces and the next field starts at the next "tab stop". But it is very hard to tell if this is true because the character spacing varies from line to line. If you set the text style to a mono-spaced font like Andale Mono, you might be able to tell if it is true.

Even so, I'm not sure it will work all the time. If the "name to search for" is longer than 10 characters and if it has a space as the 10th character, it would be parsed as two fields. Rare perhaps, but could happen.

Can you post & share a text file or Numbers file that is a list of "descriptions" that we can test ideas and formulas against? It doesn't look like anything there is sensitive but that's up to you to decide.

Excel has "array" formulas. An array formula can do the work of an entire column/row of formulas all in one cell. Someone has likely written one that will do what you need. Numbers does not have these array formulas.

VLOOKUP looks for a specified value (which could be a string) in a column or row of cells. If it is looking for "ABC", it will not find "Visa Purchase ABC". If looking for "Visa Purchase ABC" it will not find "ABC".

Not trying to be a downer. It seems like parsing the string might be the best bet but I'm not sure how to do it when the fields are separated by spaces and there may be spaces in the fields, too.

Extracting the "name to search for" from the "description" may not be feasible unless each data field in the "description" string is separated by a tab, not spaces. It was easy for the bank to stick several data fields into one string, it can be difficult to parse them back out unless there is a special character (like a tab) separating them.

If you go through one of the description cells character by character, are the gaps between the data a bunch of spaces or are they tabs? For instance in the string "Mobilepay Tracy Hansson" is there a tab character between Mobilepay and Tracy Hannson? Is it Mobilepay <tab> Tracy <space> Hansson ?

If they are tabs, it should be possible to parse the string into 4-5 pieces and search the "shop" table with each piece.