Issue with changing text in cells automatically

Discuss the spreadsheet application
Post Reply
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Issue with changing text in cells automatically

Post by allfourone »

Hi everyone, I'm having a hard time getting this task completed for a long time now so I'm taking to the forum to get some help :)

I need this to be done automatically somehow. Basically to change set text in a column to a different text. So for example:

Cell has:
Product 1 - 10

Should automatically change to:
Product 1 - 20

OR

Cell has:
Product X - 100

Should automatically change to:
Product X - 120

I know about the replace all feature but each day new cells are added which need to be changed. And to do this manually each time for 20+ products is not an option.

How can I automate this work?

Thanks
Openoffice 4.1.6 on Windows
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Issue with changing text in cells automatically

Post by FJCC »

Please explain what determines what the new text will be. The best way to do that is to upload a small spreadsheet that demonstrates what you need to do. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Issue with changing text in cells automatically

Post by Zizi64 »

Cell has:
Product 1 - 10

Should automatically change to:
Product 1 - 20
What is the condition/event/rule to change the string? Do you want to enumerate some data in a cell range and you want a value rounded to tens?

Please upload an ODF type sample file here.
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.
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

The goal is to have a feature set for a column (column A for example) where if text "product 1" is found, the text would be automatically changed to "product 2"

I would like to determine these changes by text database or something like that. Where I can add and edit these rules/entries Like:
IF FOUND "product 1" REPLACE WITH "product 2"
IF FOUND "product 3" REPLACE WITH "product 4"
etc...

Sorry, the above is just an example, not sure if commands work like that.

Something like this is even possible to have done automatically? Sorry I am not able to provide an sample file as example.

Thank you for your help
Openoffice 4.1.6 on Windows
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Issue with changing text in cells automatically

Post by RusselB »

First suggestion: Enter the information properly, so you don't have to go through and make a lot of changes.
Second suggestion (presuming you already have a lot of data that needs changing): Use Find & Replace (ctrl+f) dialog.
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.
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

I get the data from a site export (excel) and then I copy paste it into the file. I have no power over the export I get so I cannot change any details there or make sure it is written properly.

The method with Find and Replace would work but as said before I am looking for an automatic solution so each time I don't have to do Find and Replace for 20+ products
Openoffice 4.1.6 on Windows
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Issue with changing text in cells automatically

Post by RusselB »

Is the Find & Replace going to be consistant?
Ie: will the Product number searched always be increasing by one?
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.
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

The values (text) will be consintant yes.

So "product 1" will always change to "product 2". "product x" to "product y" and so on.

The above is just an example, the numbers will not be increasing by one, some products have numbers and some do not.
Openoffice 4.1.6 on Windows
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Issue with changing text in cells automatically

Post by RusselB »

Without a consistent or at least predictable base on how the text will change, a formula is, to the best of my knowledge, impossible.
In theory, a computer with true Artificial Intelligence, as depicted by the film industry, could do this, but I don't believe such a system currently exists.
You appear to have way too many inconsistencies regarding what you are starting with and what you want to end up with for this to be done via formula.
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
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Issue with changing text in cells automatically

Post by Zizi64 »

allfourone,

please upload one or two ODF type sample file here with the "before"- "after" states.
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.
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

Here are the files. Thank you all.
Attachments
After.ods
(12.21 KiB) Downloaded 91 times
Before.ods
(12.2 KiB) Downloaded 107 times
Openoffice 4.1.6 on Windows
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Issue with changing text in cells automatically

Post by Zizi64 »

H1 before :

Code: Select all

Product Name A
H1 after:

Code: Select all

Product Name Z – after
Sorry, I can not understand where the new string come from, or how you create it from the 'before string'. What is the rule, what is the relation between the "before" and "after" strings...
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.
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

Those two files I have edited manually to show what kind of result I am after. There is no rule, string or anything programmed there. That is what I was hoping you would suggest to me, what rule to use to achieve my goal.

Sorry I was unclear about that.
Openoffice 4.1.6 on Windows
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Issue with changing text in cells automatically

Post by RoryOF »

It would be simple to change Product A into Product ZA, B into ZB etc. Would that be sufficient for your purpose?

Otherwise you will have to set up some form of correspondence table such as

Code: Select all

A   V
B   W
C   X
D   Y
E   Z
Which limits you to 13 initial options and 13 final options.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

Can you tell me more about this corresponcence table. How to set this up?
Openoffice 4.1.6 on Windows
allfourone
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Post by allfourone »

Maybe substitute function will be good?

REPLACE("Product Name A";1;15; "Product Name Z")

I think I have written the formula correctly but it does not work for me for some reason...
Openoffice 4.1.6 on Windows
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Issue with changing text in cells automatically

Post by RoryOF »

allfourone wrote:Can you tell me more about this corresponcence table. How to set this up?
It would be better if you told us why you wished to change the product reference.

This is an example of the X/Y problem - you are telling us what you want to do (a solution, possibly not correct) to a problem that you have not stated.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Issue with changing text in cells automatically

Post by Sliderule »

allfourone wrote:Maybe substitute function will be good?

REPLACE("Product Name A";1;15; "Product Name Z")

I think I have written the formula correctly but it does not work for me for some reason...
I do not think you have written the formula correctly.

The third parameter is incorrect. Use either:

Code: Select all

=REPLACE("Product Name A"; 1; 14; "Product Name Z")
OR

Code: Select all

=REPLACE("Product Name A"; 1; LEN("Product Name A"); "Product Name Z")
Sliderule
Post Reply