Issue with changing text in cells automatically

[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

[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.

[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.

[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

[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.

[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

[RusselB]

Is the Find & Replace going to be consistant?
Ie: will the Product number searched always be increasing by one?

[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.

[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.

[Zizi64]

allfourone,

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

[allfourone]

Here are the files. Thank you all.

[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...

[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.

[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.

[allfourone]

Can you tell me more about this corresponcence table. How to set this up?

[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...

[RoryOF]

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.

[Sliderule]

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