Formula for calculating the scrabble value of Welsh words

Hi Ron,

Double letters make life harder (probably not only in scrabble :-P) so I dont think you can easily adopt the formula from EN version.

My proposition is: sort table with letter values with double letters on the top (I've sorted also by descending values, but it is not necessary)
then in A2 use

Formula:

Please Login or Register  to view this content.

which would remove first letter on the list from the word. If lenght of received this way word is shorter than the original, the letter (or several of them) appeared in the word). To count their scrabble value use in B2:

Formula:

Please Login or Register  to view this content.

(lenght change multiplied by letter value divided length of the letter (so change of the length = 2 for double letters is counted as one)
then copy these 2 formulas down to row 30 (as there are 29 letters in alphabet) and ib B1 sum the values from B column.

Of course less elegant than original formula.

See also in sheet2 how we can benefit of having some letters worth 1 (an why I sorted also by value).

PS. I am a bit confused by your profile vs. the footer of the message - are you using Excel 2002 or 2019 (or may be 365?)

I agree with Kaper... the double letters cause a problem. They are handled easily by Scrabble because each letter (whether single or double) is on its own tile. You, on the other hand, appear to want to write the word out within a single cell. That raises a question about double-letters in Welsh. Let's use "d" and "dd" as an example. In English, double letters can occur where each one is an individual separate letter (for example the "d" in "middle"). My question (which applies to "f" and "l" as well)... can you ever had "dd" in a word where the each "d" is an individual letter or is "dd" automatically a "single" letter when it appears?

EDIT NOTE: I see you marked this question solved, but I would still like to know the answer to my question above.

Funny plot twist with Ron / XL2002

Thanks for marking thread solved and for a reputation point.

@Rick: Not about Welsh double letters, but in Polish we do have double letters too like sz in proper name of my home city: Warszawa . But in scrable we don't have "sz" tile. We have to use s and z tiles. But to make life harder, we do have special lettrs like for instance slashed l, and e with a hook in our ex-president known in the world as Lech Walesa, while his name shall be written Wałęsa (I wonder if you can see these characters).

Just for those curious two stock images of Welsh and Polish tiles:
https://www.alamy.com/stock-photo-we...-10852254.html
https://www.picfair.com/pics/0165926...scrabble-tiles

Update

I found a different solution that uses nested substitute formulas in adjacent columns to split a word into its individual letters and double letters and then v-lookup to calculate the total scrabble value. I put the double letters in one sheet and the scrabble values of the letters (including special characters like ô, ï etc in another.

The formulas are incredibly long (especially the formula at Z3) and I think there is probably a way to simplify them...but it works.

I also think it could be adapted to other languages easily by changing the double letters and the letters in the scrabble value sheet.

I'm quite pleased with it.

Formulas

Word in A2, Value in B2, Letters in C3-Z3

Letter 1

Formula:

Please Login or Register  to view this content.

Letter 2

Formula:

Please Login or Register  to view this content.

Letter 3

Formula:

Please Login or Register  to view this content.

etc

Value

Formula:

Please Login or Register  to view this content.

I decided to take a try at this. What I developed is a UDF (user defined function) that I am pretty sure will correctly score Welsh words. Give it a try with real Welsh words that you know the correct Scrabble score for and let me know how it did. Here is the UDF that I came up with...

Please Login or Register  to view this content.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

=WelshScore(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

I take it back... the last code I posted does not work correctly. Why? Because I cannot seem to get VB to hold about 6 of your accented characters (it changes them into question marks). Excel can hold them but VB cannot. Let me think about it and see if I can come up with a work-around.