Issue with changing text in cells automatically
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Issue with changing text in cells automatically
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
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
Re: Issue with changing text in cells automatically
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Issue with changing text in cells automatically
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?Cell has:
Product 1 - 10
Should automatically change to:
Product 1 - 20
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.
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.
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
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
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
Re: Issue with changing text in cells automatically
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.
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.
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.
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
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
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
Re: Issue with changing text in cells automatically
Is the Find & Replace going to be consistant?
Ie: will the Product number searched always be increasing by one?
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.
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.
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
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.
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
Re: Issue with changing text in cells automatically
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.
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.
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.
Re: Issue with changing text in cells automatically
allfourone,
please upload one or two ODF type sample file here with the "before"- "after" states.
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.
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.
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
Here are the files. Thank you all.
- Attachments
-
- After.ods
- (12.21 KiB) Downloaded 169 times
-
- Before.ods
- (12.2 KiB) Downloaded 170 times
Openoffice 4.1.6 on Windows
Re: Issue with changing text in cells automatically
H1 before :
H1 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...
Code: Select all
Product Name A
Code: Select all
Product Name Z – after
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.
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.
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
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.
Sorry I was unclear about that.
Openoffice 4.1.6 on Windows
Re: Issue with changing text in cells automatically
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
Which limits you to 13 initial options and 13 final options.
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
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
Can you tell me more about this corresponcence table. How to set this up?
Openoffice 4.1.6 on Windows
-
- Posts: 8
- Joined: Wed Jul 17, 2019 1:07 am
Re: Issue with changing text in cells automatically
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...
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
Re: Issue with changing text in cells automatically
It would be better if you told us why you wished to change the product reference.allfourone wrote:Can you tell me more about this corresponcence table. How to set this up?
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
Re: Issue with changing text in cells automatically
I do not think you have written the formula correctly.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...
The third parameter is incorrect. Use either:
Code: Select all
=REPLACE("Product Name A"; 1; 14; "Product Name Z")
Code: Select all
=REPLACE("Product Name A"; 1; LEN("Product Name A"); "Product Name Z")