[Solved] Calculating numerical value of words

Discuss the spreadsheet application

[Solved] Calculating numerical value of words

Postby orangeli » Thu Aug 15, 2019 9:28 pm

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
orangeli
 
Posts: 83
Joined: Wed May 25, 2011 6:45 pm

Re: Calculating numerical value of words

Postby RusselB » Thu Aug 15, 2019 9:52 pm

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.
User avatar
RusselB
Moderator
 
Posts: 5663
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calculating numerical value of words

Postby Lupp » Thu Aug 15, 2019 10:43 pm

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
User avatar
Lupp
Volunteer
 
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calculating numerical value of words

Postby RoryOF » Thu Aug 15, 2019 10:54 pm

Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29868
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calculating numerical value of words

Postby orangeli » Fri Aug 16, 2019 7:34 am

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

Postby keme » Fri Aug 16, 2019 9:16 am

User avatar
keme
Volunteer
 
Posts: 3284
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Calculating numerical value of words

Postby MrProgrammer » Sat Aug 17, 2019 9:20 pm

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3891
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calculating numerical value of words

Postby RoryOF » Sat Aug 17, 2019 9:29 pm

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
User avatar
RoryOF
Moderator
 
Posts: 29868
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Calculating numerical value of words

Postby karolus » Sun Aug 18, 2019 10:28 pm

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
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am


Return to Calc

Who is online

Users browsing this forum: laul07, MSN [Bot] and 36 guests