Page 1 of 1

Issue with changing text in cells automatically

PostPosted: Wed Jul 17, 2019 1:20 am
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

Re: Issue with changing text in cells automatically

PostPosted: Wed Jul 17, 2019 4:10 am
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.

Re: Issue with changing text in cells automatically

PostPosted: Wed Jul 17, 2019 6:55 am
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.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 12:05 am
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

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 1:23 am
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.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 1:35 am
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

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 1:57 am
by RusselB
Is the Find & Replace going to be consistant?
Ie: will the Product number searched always be increasing by one?

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 2:10 am
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.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 3:33 am
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.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 6:09 am
by Zizi64
allfourone,

please upload one or two ODF type sample file here with the "before"- "after" states.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 1:02 pm
by allfourone
Here are the files. Thank you all.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 3:58 pm
by Zizi64
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...

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 4:13 pm
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.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 4:30 pm
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   Expand viewCollapse view
A   V
B   W
C   X
D   Y
E   Z


Which limits you to 13 initial options and 13 final options.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 4:59 pm
by allfourone
Can you tell me more about this corresponcence table. How to set this up?

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 5:08 pm
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...

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 5:13 pm
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.

Re: Issue with changing text in cells automatically

PostPosted: Thu Jul 18, 2019 8:15 pm
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   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