Using Office / Excel 365 I have written a query that extracts the "make" from a text string by referencing a list of makes but with so many similar names how do I get the correct one extracted consistently?
Example text Strings:
FF Industrial FF-13 Mini Excavator Unused
FF QH-15 Mini Excavator Unused
Make ref table data
Look up Ref Output data
FF Industrial FF Industrial
FF *FIX FF Industrial
The correct Make is "FF Industrial" but as the data I work with is inconsistently typed my backup is to use the *FIX prefix so the strings that just have FF as the make get the *FIX FF Industrial output but the ones that are correct should get the correct output but that is not always the case.
As I live with manually fixing the "*FIX" examples I want to reduce this as much as I can.
I have tried setting the ref data sorted in Z to A order and the text string in A to Z order as I presume the data is processed in order so I'm expecting that the "FF Industrial" example should be seen before "FF " and provide the correct output.
My data set could have from one to several hundred lines for this make combination alone in a data set of hundreds to 25,000 rows and there are dozens of other makes with multiple words I need to clean with the Make ref table having over 13,000 lines.
My file sits in a password protected Onedrive file so can't provide an upload sorry. If more info is needed please let me know.
Any tips, tricks or cold hard lessons would be appreciated.
I have searched the MOTH and other YouTube channels but can't find a solution and may well be due that I'm not using the correct terminology.
Thanks in advance guru's
Tony N
I see that there are several views but no replies. Maybe I am wrong but as I understand it if FF is at the beginning of the text, then you want a return of FF Industrial no matter what is after the FF.
If this the case, I do not understand why the *FIX FF INDUSTRIAL output. So, I must be mistaken in my interpretation.
Thanks Cedric
I should have gone onto explain that the “make” output is used to extract the make from the text string based on its length so it needs to match and the *FIX is used to stop that process.
the goal is the leave the remaining text that should be the model details
I'm sorry, but I am old, and my mind does not work fast. I still do not get what the final output should be. From what I understand this is the data you need fixed:
FF Industrial FF-13 Mini Excavator Unused
FF QH-15 Mini Excavator Unused
You want to convert that into:
FF Industrial FF-13 Mini Excavator
FF Industrial QH-15 Mini Excavator
To do that you have a list of items to compare with the submitted data. If that is the case, it is probably doable.
It would help if you could submit an example table of about 10 rows of what the data looks like and what you want the results to be.
