Numeric or Alphanumeric Codes to Words

[MPar]

Hi everyone, I have this personal project in which I want to convert various numeric or alphanumeric codes to the words that are in a word pool. Since I have inadequate Excel knowledge for how to do this, I wanted to ask about it here, whether if it is doable or not and how can I realize it.

I think describing what exactly I want to do with giving examples would be the best way to go, so I'm gonna try that.

Lets say I have a coding system something like this:

0: v, w
1: t, d
2: n
3: c, f
4: m, r
5: l

So with this coding system (and using the rest of the alphabet if necessary) the words in the following word pool would be converted to numeric values like below:

water ---> 014
meridian ---> 4412
car ---> 34
lemon ---> 542
mice ---> 43
wider ---> 014
avatar ---> 014
tendency ---> 12123
war ---> 04
watered ---> 0141
fancy ---> 323
watery ---> 014
martian ---> 4412
chief ---> 33
told ---> 151
wader ---> 014
old ---> 51
ice ---> 3
marathon ---> 4412
tall ---> 155
wrong ---> 042
waiter ---> 014
after ---> 314
another ---> 214


The main thing I want is Excel to fetch all the appropriate words from the word pool according to the coding system when I enter a numeric or alphanumeric value.

Example 1:

When I enter "014" I should get the following result.

014 ---> (v, w) - (t, d) - (m, r)

water
watery
waiter
wider
avatar
wader


Example 2:

When I enter "4412" I get the following result.

4412 ---> (m, r) - (m, r) - (t, d) - n

marathon
meridian
martian


Example 3:

If I enter "4a41h2" instead of "4412" I get the word "marathon" only.

4a41h2 ---> (m, r) - a - (m, r) - (t, d) - h - n

marathon


So, I think that's it more or less. Any help and ideas will be much appreciated. Thanks for reading.



Note 1: The letters that are not in the coding system are not relevant so they can be used in words freely. (all the vowels and some consonants)

Note 2: Unlike in the example, I will use all the numerals (from 0 to 9) in the coding system.

Note 3: The world pool most probably will be huge, like including thousands of words, if it is feasible of course.

Note 4: I have been using Excel for basic office works for several years but I'm not an advanced user by any means. But I'm also willing to learn new things while working on this.

Note 5: I am not after making someone do some dirty work for me or anything. This is an entirely personal project, I just want to know if this is doable within Excel and if it is, I am just seeking some guidance and suggestions for the main workflow or plans to make it happen.

[MPar]

No ideas?

If not, is there any other places I can ask this?

[jindon]

UDF

Use like

=GetWords(A1,$G$1:$G$24)

where A1 holds Numbers like 014
$G$1:$G$24 holds list of words

Please Login or Register  to view this content.

[mehmetcik]

This is quite simple really.

More importantly it is interesting

I will create something for you

[MPar]

UDF

Use like

=GetWords(A1,$G$1:$G$24)

where A1 holds Numbers like 014
$G$1:$G$24 holds list of words

Please Login or Register  to view this content.

Thanks a lot for the help. I will play around with this and see what I can do.

This is quite simple really.

More importantly it is interesting

I will create something for you

Oh, that would be great and really helpful. And I hope it is simple indeed. Thank you very much in advance for your time and help.

[mehmetcik]

I decided against a UDF

I am planing to create three Macros.

The First creates the code for 26000 words in a couple of minutes.

The code is in Columns A and B of A spreadsheet to make it easy to ammend

The list of words is in column F

The macro puts the code into column G and then sorts by column G.

I downloaded the "sowpods" list of words from

http://www.freescrabbledictionary.com/sowpods/

and pasted them into column F. Too Big to post here unfortunately.

Please Login or Register  to view this content.

[mehmetcik]

I have created the second and third macros.

enter #0 or #1 into A2 or a word in D2


**************************************************************************

This one is sheet specific so right click on the sheetname sowpos at the bottom of excel and select view code to see and edit the code:-

Please Login or Register  to view this content.

The Original Code has been modified slightly in preparation for my third macro.

Please Login or Register  to view this content.

[MPar]

^^

Wow! I'm literally speechless! This is by far the best and the most elaborate help I have received on an internet forum ever! I cannot thank you enough sir, I wish I had higher reputation points to thank you! And yes this was exactly what I was looking and aiming for. And you made it very easy to modify the code as well. All perfect!

Now I'm really glad that I asked for help here. This could have taken weeks for me to accomplish or maybe even more or never, because I've been already trying to do this for about a week now and I was not even close to make anything useful or meaningful at all. Although, I'm still going to study on this quite a bit because I think there are too many good stuff here to learn. Plus that way, I could make even further modifications for my needs, once I get how it exactly works. Btw, it is definitely very modest of you to call this as simple!

Once again thank you very very much, sir. It's so much appreciated!

Btw, should I mark this thread as solved immediately? Asking because I am going to spend a lot of time on the solution I got in the weekend and I wouldn't want to create another thread if I had further questions about this. Would it be alright if I waited till the Monday at least?

[JBeaucaire]

Marking it as solved will not stop you from adding followup posts in this thread later.