[Solved] I would like to lookup and replace numbers

Discuss the spreadsheet application
Post Reply
Openuser12345
Posts: 5
Joined: Fri Sep 08, 2017 5:38 am

[Solved] I would like to lookup and replace numbers

Post by Openuser12345 »

This is the problem,
i want to replace numbers to text on the sheet.

Solution, would be to lookup a table and update the original list accordingly.

I hope the Question is clear, thanks for any help!
Attachments
This is the solution, i want calc to lookup and replace from.
This is the solution, i want calc to lookup and replace from.
SOL.jpg (13.86 KiB) Viewed 2057 times
This is the problem, i want to replace numbers to text.
This is the problem, i want to replace numbers to text.
prob.jpg (18.68 KiB) Viewed 2057 times
Last edited by Hagar Delest on Fri Sep 08, 2017 10:19 pm, edited 1 time in total.
Reason: tagged [Solved].
Openoffice 3.1.1 on Win 7
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: I would like to lookup and replace numbers.

Post by robleyd »

Will the price always be a whole number? If not, how should a decimal value be handled?
 Edit: See also this post and this post 
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Openuser12345
Posts: 5
Joined: Fri Sep 08, 2017 5:38 am

Re: I would like to lookup and replace numbers.

Post by Openuser12345 »

Hello David,
Thanks for the reply!

I saw the pages you referred, seems similar to the second page,

However, the codes are NOT going to be A=1...B=2... C=3.
Lets Assume, 1234567890=openoffice
But the idea remains same, change numbers to text... (to hide the value)!
squenson wrote:One of the reasons the formula may not work is that you are using a separator for arguments, in the formula, which is different from mine, the comma. I attach a file that should automatically adapt to your settings.
:bravo:
If i could understand how to edit this and change ABC=123 into my codes... my job will be done!
Openoffice 3.1.1 on Win 7
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: I would like to lookup and replace numbers.

Post by robleyd »

However, the codes are NOT going to be A=1...B=2... C=3.
Lets Assume, 1234567890=openoffice
Does not compute! In this case, e is both 3 and 0; and o is both 1 and 5.

Please explain exactly what rules you wish to use as the solution offered may, nay will, vary depending. For instance, you might use:

Code: Select all

0 1 2 3 4 5 6 7 8 9
^ ^ ^ ^ ^ ^ ^ ^ ^ ^
b l a c k s m i t h
where each character has a unique matching number and the key is a simple word to remember.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11354
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: I would like to lookup and replace numbers.

Post by Zizi64 »

Do you want encode the numbers (prices) in your file?

Is not enough to hide them?

If you encode the numbers (substitute them by letters) then you will not able calculate with them (SUM or other), because the result will be a text, but not a number.

Do you want to convert the visible view of the numbers only (as they are was formatted), or all of the numeric characters of the value (decimal fraction part)?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Openuser12345
Posts: 5
Joined: Fri Sep 08, 2017 5:38 am

Re: I would like to lookup and replace numbers.

Post by Openuser12345 »

robleyd wrote:
However, the codes are NOT going to be A=1...B=2... C=3.
Lets Assume, 1234567890=openoffice
Does not compute! In this case, e is both 3 and 0; and o is both 1 and 5.
:knock: Opps. my bad. Sorry that was just an (bad) example.
I will PM you the code. thanks
Openoffice 3.1.1 on Win 7
Openuser12345
Posts: 5
Joined: Fri Sep 08, 2017 5:38 am

Re: I would like to lookup and replace numbers.

Post by Openuser12345 »

Zizi64 wrote:Do you want encode the numbers (prices) in your file?

Is not enough to hide them?

If you encode the numbers (substitute them by letters) then you will not able calculate with them (SUM or other), because the result will be a text, but not a number.

Do you want to convert the visible view of the numbers only (as they are was formatted), or all of the numeric characters of the value (decimal fraction part)?
Yes this is going to hide numbers with a magic word.
There will be no decimals, the number prices are not going to be printed, of course.
There will be no calculations after code is created, will only read it on paper.

So, the prices will be completely hidden on paper. :bravo:
Openoffice 3.1.1 on Win 7
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: I would like to lookup and replace numbers.

Post by robleyd »

I will PM you the code
Received via email; but again, you have duplicate characters in the code - the third and sixth characters are repeated later in the string. You need unique characters, as in the example I gave above.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: I would like to lookup and replace numbers.

Post by keme »

Attached a file. Are you looking for something like this? (Limited to max 14 digits, but easy to extend.)

For suitable coding keys, see https://www.morewords.com/unique-letters/
Attachments
Code reversal.ods
(12.37 KiB) Downloaded 71 times
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Openuser12345
Posts: 5
Joined: Fri Sep 08, 2017 5:38 am

Re: I would like to lookup and replace numbers.

Post by Openuser12345 »

keme wrote:Attached a file. Are you looking for something like this? (Limited to max 14 digits, but easy to extend.)

For suitable coding keys, see https://www.morewords.com/unique-letters/
@keme - Thanks! Your solution is actually changeable and perfectly working. I just don't understand how to make it work.. A lot of functions running! :(

I worked on @squenson 's solution on viewtopic.php?f=9&t=45844

And this is what i'm up to... the simple function is working fine, except when i repeat numbers. :knock:

Code for BLACKSMITH, assuming number to be coded stored on A3

Code: Select all

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3;"00";"JK");"11";"AK");"22";"BK");"33";"CK");"44";"DK");"55";"EK");"66";"FK");"77";"GK");"88";"HK");"99";"IK");"0";"H");"1";"B");"2";"L");"3";"A");"4";"C");"5";"K");"6";"S");"7";"M");"8";"I");"9";"T")
Attachments
substitute.ods
(14.19 KiB) Downloaded 96 times
Openoffice 3.1.1 on Win 7
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: I would like to lookup and replace numbers.

Post by keme »

Openuser12345 wrote:...
@keme - Thanks! Your solution is actually changeable and perfectly working. I just don't understand how to make it work.. A lot of functions running! :(
...
Not really. Only four different formulas, each copied across a suitable range:
B3:B12 - Simple function call to extract single letter for each digit
C16:P16 - Formulas to "transliterate" from single digits to corresponding letters. (Same formula in all cells, just varying character number.)
C17:P17 - Essentially the same formula as above, only a small reference change for backwards transliteration - letters to digits.
B17 and B18 - Stringing together single digits from encoding in C16:P16 and decoding in C17:P17.

If you want to examine the formulas, you only need to look at one from each range. They all work exactly the same.
VLOOKUP could have been used for the encoding part. Since I decided beforehand that I was going to do it both ways - encoding and decoding - it was easier to use the more elaborate INDEX(MATCH(...)) construct which could be copied with minimal changes to do the decoding.


Two input cells:
B1 - Key for encoding/decoding.
B16 - Number to be encoded.
User avatar
Zizi64
Volunteer
Posts: 11354
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: I would like to lookup and replace numbers.

Post by Zizi64 »

For the "Substitute" approach:

Not needed the double numbers in your formula. You need 10 parts only; one for each number: 0123456789.

Code: Select all

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3;"0";"H");"1";"B");"2";"L");"3";"A");"4";"C");"5";"K");"6";"S");"7";"M");"8";"I");"9";"T")
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply