Change last character(s) in multiple words in a cell

Hello!

I'd like to know how to change the last character(s) (or add or delete a character) in each word, when there are 2 or more words in a cell. Of course, this change or replacement should be based on certain rules, such as (I apologize, but I'll have to write all the examples in my language):

if any words in a cell ends with "ev", it should be changed so that it ends with "evega" (for example natrijev > natrijevega)
if any words in a cell ends with "id", it should be changed so that it ends with "ida" (for example klorid > klorida)
if any words in a cell ends with "na", it should be changed so that it ends with "ne" (for example kislina > kisline)
and so on....
...


So if I have "natrijev klorid" written in a cell it should be changed to "natrijevega klorida" or "acetilsalicilna kislina" should be changed to "acetilsalicilne kisline" or "natrijev metamizolat monohidrat" should be changed to "natrijevega metamizolata monohidrata".

I think it would be easier using a formula, but I kinda do not know how to start.
So I would very much appreciate your help.


Regards

Originally Posted by Speshul

=substitute(a1,"ev","evega")

That would change

"every week has seven days" to "evegaery week has sevegaen days"

We want specifically only if the word 'Ends' with ev

Try
=substitute(a1&" ","ev ","evega ")

send as sample of the cells in your language

also include in a separate sheet the rules
A1 ev B1 evega
A2 id B2 ida
A3 na B3 ne...

Thank you all for the quick response. Since I'm no longer at work and I do not have access to the file, I'm going to finally test the proposed solution tomorrow. But after a quck test I've done I think that Speshul's solution

Originally Posted by Speshul

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"ev","evega"),"id","ida"),"na","ne")

with Jonmo1's correction

Originally Posted by Jonmo1

Try
=substitute(a1&" ","ev ","evega ")

should work.

Originally Posted by rcm

send as sample of the cells in your language

also include in a separate sheet the rules
A1 ev B1 evega
A2 id B2 ida
A3 na B3 ne...

rcm thank you for your offer.
If it turns out that the proposed solution does not work, I will send you the sample and rules.

Regards

Today I tested the proposed solution. Unfortunately, I have to say that the proposed solution does not work in all cases. The problem is related to the order of the rules in the formula.

As you can see on the sheet with examples in the attached file, I get different results when rules were sorted alphabetically by ascending or descending order. Errors are the result of the fact that one rule invalidate the other.

For Example

How it's written now	How should be	what I get using formula1*	what I get using formula2**
etanercept	etanercepta	etanercepta	etanercepte

^*Formula 1: Rules sorted from Z to A
^**Formula 2: Rules sorted from A to Z

If you look at the rules sheet, you can see that words ending with "ept" should change to end with "epta". The other rule requires that the ending "ta" is changed to "te".
The order of the rules in the formula is obviously important. When the rule "ept" to "epta" is before the rule "ta" to "te" (as in formula2), then the result is wrong (as in marked in the above table).

I got the table yesterday and at first glance it seemed to me that I will not need so many rules, so that a simple formula would be quite enough.
After more detailed checking I realized that I would need at least 60 different rules.
The table is quite large, almost 20,000 records (and records are still being added). I'm not the administrator of the original table and have no impact on how the data is entered into the the table.

So any additional suggestion or solution will be appreciated.

Regards

hi
i think the attached sheet wil help you.....

OK, I have a working VBA code that does all the rules without Re-Doing one that was already done by a previous rule.
However, I don't understand the reasoning behind the ones you marked to NOT change.

How can any solution we provide know which ones to NOT apply the changes to?

Example book

EFfrankt68.xlsm

Originally Posted by mohamedJamsheer

hi
i think the attached sheet wil help you.....

Thanks for your help, but unfortunately the results are correct only for cells with only one word in them. For cells with two or more words, only the last word is correctly converted.

Originally Posted by Jonmo1

Example book

Attachment 336337

Thank you, iz works!

Regarding your previous remark

Originally Posted by Jonmo1

OK, I have a working VBA code that does all the rules without Re-Doing one that was already done by a previous rule.
However, I don't understand the reasoning behind the ones you marked to NOT change.

How can any solution we provide know which ones to NOT apply the changes to?

All cells that contain punctuation (eg, comma, full stop, semicolon, dash), square brackets, parentheses and numbers, I need to check manually, because they do not comply with any rule. Fortunately, there are not many of such records, and can be easily filtered out.

As I said, I'm very pleased with the solution, but I still have one more request. Would you be so kind and explain to me how this function works, so I would also understand it?

Regards