Hange last characters in multiple words in a cell

[frankT68]

Hi!

I would like to know how can I change the last character (or add or delete a character) in each word, when there are 2 or more words in a cell?

For example:

How is written	How I would like to be written
whit hors	white horse
white agent	whitening agents

<tbody>
</tbody>

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

Frank

 

[frankT68]

It shuld be Change and not Hange in the title...

 

[Andrew Poulsom]

Welcome to MrExcel.

For your first example:

=TRIM(SUBSTITUTE(A1&" "," ","e "))

 

[frankT68]

Welcome to MrExcel.

For your first example:

=TRIM(SUBSTITUTE(A1&" "," ","e "))

Thanks, but as you have already mentioned, it is unfortunately useful only for the first example.

 

[XOR LX]

But how do we know what is to be appended in each case? In one of your examples it is "e", in the other it is "ning" - how can we possibly know what you wish to append?

Regards

 

[frankT68]

Re: Change last characters in multiple words in a cell

But how do we know what is to be appended in each case? In one of your examples it is "e", in the other it is "ning" - how can we possibly know what you wish to append?

Regards

Well, I'll try to explain better, even though my English is a bit rusty.
As I said before, 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 hope I managed to explain better what I would like to do.


Regards

 

[XOR LX]

Re: Change last characters in multiple words in a cell

• and so on....

But this "and so on..." is precisely the problem. How many rules are there in total?

Before even attempting any solution, a definitive list of all such rules would be required: Excel doesn't understand "and so on"!

Regards

 

[frankT68]

Re: Change last characters in multiple words in a cell

I'm perfectly well aware that

Excel doesn't understand "and so on"!

Unfortunately, I have a table with about 20,000 records, so I find it kinda impossible to determine all the rules precisely. I did not prepare this table which is, on top of all, very inconsistent (eg, incorrect word order), I just have to work with it.
I would be very happy if I could get advice to start with for cases which I have have already mentioned. This could be a good basis to build on.

Regards

 

[frankT68]

Re: Change last characters in multiple words in a cell

For all those who are interested in the solution of this problem - the answer can be found here and here.