How do I extract a string from a field that matches a text field in one of the records from another table?

[johnfromberkeley]

I'm not sure if this is the right section in the forums.

I have a table of product titles with a single field containing text like this:

Sony headphones

Bose headphones

Panasonic headphones

I have a separate table that is a list of brands, like this:

Sony

Bose

Panasonic

How do identify the brand name in the product titles, by checking each product title to see if it matches one of the brand records? I'd like output like this:

(field 1)Sony headphones  (field 2)Sony

Bose headphones | Bose

Panasonic headphones | Panasonic

In other words, I have a list of product titles, and I have to classify each record against a list of brands.

I'm not looking for a data validation solution, like a picker. I'm looking to automate the task of populating the "brand" field of each record as it is imported.

 

[comment]

Is this a one-time conversion project, or do you plan to do this often?

[johnfromberkeley]

I plan to do this on an ongoing basis.

For the time being, it's easy to do in excel, and my files are starting as excel files anyway. So, I can dummy the functionality in for the time being, but I need something that will work dynamically against a changing list of brand names.

Thank you.

[comment]

A simple - perhaps too simple - approach would use a calculation field in the Products table =

Substitute ( Description ; " " ; ¶ )

You can then use this as the matchfield for the relationship between Products and Brands.

However, there are several caveats here:

• spaces do not always cleanly delimit words; for example, you could and up with "Panasonic's" or "Sony®" which would not match a brand;
• at least in theory, you could have two or more brands matching the same description e.g. "... Bose, unlike Panasonic..".
• some brands may contain more than one word.

So a more meticulous solution would have to search the Description field in a loop for each individual brand.

How exactly are you doing this in Excel?

 

Edited June 29, 2016 by comment