## [Solved] Calculating numerical value of words

### [Solved] Calculating numerical value of words

Hi

A text is given in a cell.
The task is to calculate a numerical value for each word.
The letter "a" equals 1, the letter "b" equals 2, the letter "c" equals 3 etc.
The numerical value of word equals the sum of the numerical values of its letters (i.e. the word "add" has a numerical value of 9 (1+4+4)).
My questions:
Is there a function who can break a text and assign each word into a different cell?
Is there a way to perform the numerical value calculation of a word according to above description?

Thank you!
Last edited by robleyd on Mon Aug 19, 2019 1:06 am, edited 2 times in total.
LO 6.1.5.2 Debian
orangeli

Posts: 83
Joined: Wed May 25, 2011 6:45 pm

### Re: Calculating numerical value of words

The quick answer, is Yes it can be done.
The problems come about for handling the non-text characters... like spaces, commas, periods, question marks, etc.
While the can be handled using the same formula to calculate the letter values, your sums could be significantly higher than you are expecting.

There isn't a single function to do this, but if you have a maximum number of characters (up to 1000) then this can be done in a spreadsheet.
If you don't have a maximum, or don't know what the maximum length could be, then a macro containing a loop would be, I think, your only option.
In either case, I think the macro option would be your best option.
Regretfully, my knowledge of coding macros is still very limited, so I'll need to research and test first.... someone else on the forum that is more familiar with macros may be able to post something sooner.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5663
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Calculating numerical value of words

As already hinted by RusselB, the first technical task is to define exactly what a "word" is in your context, and to split the text content of your cells into words. Calc has no standard functions efficiently supporting the split. The BASIC included with AOO and usabel for "macro" programming has. But even if you write some user code to have the functionality of splitting availabe in Calc, there are no ways to RETURN split sequences of arbitrary numbers of words to a spreadsheet one word per cell. If you want to avoid this kind of split, you would need to output the arbitrary number of sums (one per word) somewhere. How?

However, there are non-technical aspects:
What use, what sense?
MOST languages in the world use specific letters disregarded by so-called numerology. Many haven't even a way to write their words in any alpabetic manner.

Insisting on the theorem that only languages written in "pure latin alphabet" are relevant, and that this alphabet consists of exactly 26 characters in a once defined order, which also is used in current computing, and without a need to distinguish Upper/Lower case in the context, it's simple.

For a SINGLE pure-latin-word not accompanied by whitespace characters in a cell, say A2, the "numerical value" of that word can be achieved by
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(CODE(UPPER(MID(A2;COLUMN(OFFSET(INDIRECT("a1");0;0;1;LEN(A2)));1)))-64)

As it seems from previous threads concerning similar topics, mostly "continued digit-sums" ending with a single digit which not shall be 0 are the goal. Thereby the universe of words is partitioned in exactly 9 classes.
If you also want to proceed this way, use
Code: Select all   Expand viewCollapse view
=MOD(SUMPRODUCT(CODE(UPPER(MID(A2;COLUMN(OFFSET(INDIRECT("a1");0;0;1;LEN(A2)));1)))-64);9)+IF(CURRENT()=0;9;0)
as the appropriate completion of the above given formula.
Last edited by Lupp on Thu Aug 15, 2019 11:08 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Calculating numerical value of words

Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 29868
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: Calculating numerical value of words

Thank you RusselB and RoryOF, Danke Lupp!
The use case is for non Latin characters.
I was hoping to avoid a macro with some reverse-concatenate function and an elegant calculation as suggested by Lupp.
LO 6.1.5.2 Debian
orangeli

Posts: 83
Joined: Wed May 25, 2011 6:45 pm

### Re: Calculating numerical value of words

keme
Volunteer

Posts: 3284
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Calculating numerical value of words

orangeli wrote:The letter "a" equals 1, the letter "b" equals 2, the letter "c" equals 3 etc. The task is to calculate a numerical value for each word.
Calc is not a good tool for this task. Perl would be better. Given a file of text, this Perl program computes numerical values for each word using 1 for a or A, 2 for b or B, …, through 26 for z or Z.

my (\$n,%codes);
@codes{'A'..'Z'} = 1..26; # Initialize code table
while (<>) { # Process each line
foreach (split) { # Process each word
print; \$n=0; # Print the word
\$n+=\$codes{+uc}//0 foreach (split ''); # Process each character
print " \$n\n"; # Print sum of codes
}
}

Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3891
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Calculating numerical value of words

I think it could also be done quite easily in python, but I have not time (nor inclination) to work that out.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 29868
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: [Solved] Calculating numerical value of words

Hum… Python:

Code: Select all   Expand viewCollapse view

from string import ascii_uppercase as abc
# -> 'A…Z'

abc = '_%s' % abc                        # - > '_A…Z'

def num_value( word ):
return sum
(map(abc.index, word.upper()))

AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17

karolus
Volunteer

Posts: 852
Joined: Sat Jul 02, 2011 9:47 am