[Solved] Calculating numerical value of words

Discuss the spreadsheet application
Post Reply
orangeli
Posts: 84
Joined: Wed May 25, 2011 6:45 pm

[Solved] Calculating numerical value of words

Post by orangeli »

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.
Reason: Add green tick
LO 6.1.5.2 Debian
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calculating numerical value of words

Post by RusselB »

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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calculating numerical value of words

Post by Lupp »

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

=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

=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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calculating numerical value of words

Post by RoryOF »

Something similar to this has come up before, I think in the threads at
viewtopic.php?f=9&t=95269
and at
viewtopic.php?f=9&t=86198
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
orangeli
Posts: 84
Joined: Wed May 25, 2011 6:45 pm

Re: Calculating numerical value of words

Post by orangeli »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Calculating numerical value of words

Post by keme »

User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calculating numerical value of words

Post by MrProgrammer »

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
   }
}
This is not a Perl forum so if you have questions about this program find one and ask there.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calculating numerical value of words

Post by RoryOF »

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.15 on Xubuntu 22.04.4 LTS
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] Calculating numerical value of words

Post by karolus »

Hum… Python:

Code: Select all

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 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Post Reply