Dec 02 2022 05:09 PM
Dec 02 2022 05:09 PM
I have a column of product numbers, within each number is one letter that represents the type of product it is. How do I marry that letter up to a description from another list so that I can categorise every product number?
226002T0049 this would be T for tyre.
What formula do I use to extract the letter T and assign it to the word Tyre?
thanks for your help.
Dec 02 2022 06:13 PM
Dec 02 2022 11:06 PM
Assuming you run Excel 2021 or 365 a formula based alternative:
=LET( LetterPos, SUM(IFERROR(SEARCH(CHAR(SEQUENCE(26,,97)),B5),0)), Letter, MID(B5, LetterPos, 1), Category, XLOOKUP(Letter, TBL_MAPPING[Letter], TBL_MAPPING[Category]), IF(LetterPos, Category, "No letter found") )
Dec 03 2022 01:32 PM
Dec 03 2022 01:45 PM
Dec 03 2022 01:53 PM
Dec 03 2022 02:12 PM
This is done with Power Query. You can start the Power Query Editor as shown in the screenshot.
Then you can follow the applied steps within the Power Query Editor. "Angewendete Schritte" are the applied steps of the query. You can click on every step from "Source" to "Description" in order to follow the transformation of the data.
If you want to know more about Power Query i'd recommend this site:
Dec 03 2022 03:37 PM
@Quadruple_Pawn Ok, now I understand. But I have a problem when I merge the query I cannot I get a recognition of the letter buried in the middle of the product number it just produces a column of null and as consequence the category name is all null as well. What am I doing incorrectly? Thank you
Dec 04 2022 01:06 AM
@Eqa33 Added a few steps to your query. These extract the letter codes and merge them with the ones in the Code table. See if this does what you need.
Dec 04 2022 02:51 AM
Dec 04 2022 03:43 AM - edited Dec 04 2022 03:46 AM
@Eqa33 Upload the file please. I need to see the Merge step. What you are merging and how. Did you extract the letter code in any of the other queries, by the way?
Dec 04 2022 04:18 AMSolution
@Eqa33 You missed the most important part and that is that you need to extract the one Letter from the code first. You are now trying to merge the full product code in the Sales table with the category code in the Category table. Obviously, that's not going to return any match.
I've added the step that extracts the code and changed the merge query. It works now.
Dec 04 2022 04:31 AM
Dec 04 2022 04:49 AM
@Eqa33 The last step of the 2022 Sales SOH query creates a custom column that takes the text from Column1 and removes any dot "." or number from "0" to "9", thus leaving only the single letter.
Now, in the Source step of the Merge1 query you select Custom and Code as the matching columns.
Dec 04 2022 06:38 PM