Issue with changing text in cells automatically

Discuss the spreadsheet application

Issue with changing text in cells automatically

Postby allfourone » Wed Jul 17, 2019 1:20 am

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
allfourone
 
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Postby FJCC » Wed Jul 17, 2019 4:10 am

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7298
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Issue with changing text in cells automatically

Postby Zizi64 » Wed Jul 17, 2019 6:55 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8318
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Issue with changing text in cells automatically

Postby allfourone » Thu Jul 18, 2019 12:05 am

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
allfourone
 
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Postby RusselB » Thu Jul 18, 2019 1:23 am

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 and LibreOffice 6.0.6.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: 5485
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Issue with changing text in cells automatically

Postby allfourone » Thu Jul 18, 2019 1:35 am

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
allfourone
 
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Postby RusselB » Thu Jul 18, 2019 1:57 am

Is the Find & Replace going to be consistant?
Ie: will the Product number searched always be increasing by one?
OpenOffice 4.1.7 and LibreOffice 6.0.6.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: 5485
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Issue with changing text in cells automatically

Postby allfourone » Thu Jul 18, 2019 2:10 am

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
allfourone
 
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Postby RusselB » Thu Jul 18, 2019 3:33 am

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 and LibreOffice 6.0.6.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: 5485
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Issue with changing text in cells automatically

Postby Zizi64 » Thu Jul 18, 2019 6:09 am

allfourone,

please upload one or two ODF type sample file here with the "before"- "after" states.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8318
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Issue with changing text in cells automatically

Postby allfourone » Thu Jul 18, 2019 1:02 pm

Here are the files. Thank you all.
Attachments
After.ods
(12.21 KiB) Downloaded 8 times
Before.ods
(12.2 KiB) Downloaded 8 times
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

Postby Zizi64 » Thu Jul 18, 2019 3:58 pm

H1 before :
Code: Select all   Expand viewCollapse view
Product Name A


H1 after:
Code: Select all   Expand viewCollapse view
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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8318
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Issue with changing text in cells automatically

Postby allfourone » Thu Jul 18, 2019 4:13 pm

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
allfourone
 
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Postby RoryOF » Thu Jul 18, 2019 4:30 pm

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   Expand viewCollapse view
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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29552
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Issue with changing text in cells automatically

Postby allfourone » Thu Jul 18, 2019 4:59 pm

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

Postby allfourone » Thu Jul 18, 2019 5:08 pm

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
allfourone
 
Posts: 8
Joined: Wed Jul 17, 2019 1:07 am

Re: Issue with changing text in cells automatically

Postby RoryOF » Thu Jul 18, 2019 5:13 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29552
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Issue with changing text in cells automatically

Postby Sliderule » Thu Jul 18, 2019 8:15 pm

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   Expand viewCollapse view
=REPLACE("Product Name A"; 1; 14; "Product Name Z")

OR

Code: Select all   Expand viewCollapse view
=REPLACE("Product Name A"; 1; LEN("Product Name A"); "Product Name Z")

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests