Forum Discussion
How to match and assign a word to a letter in a cell
Hi There,
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?
Example;
Product No.
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.
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.
- Harun24HRBronze ContributorProvide few more sample and your desired output to make it clear. Better attach a sample file to your post so that we can download the file and work on it.
- Eqa33Brass Contributor
- Riny_van_EekelenPlatinum Contributor
- Eqa33Brass ContributorBut how do I do that and then how does T become Tyre, and B become Bat, and C become Cap, etc?
- OliverScheurichGold Contributor
With Riny_van_Eekelen 's data you can add another blue dynamic table where you enter the descriptions. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
- LorenzoSilver Contributor
Hi Eqa33
Assuming you run Excel 2021 or 365 a formula based alternative:
in C5:
=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") )