Hange last characters in multiple words in a cell

frankT68

New Member
Joined
Jul 30, 2014
Messages
9
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 horswhite horse
white agentwhitening 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
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Jul 30, 2014
Messages
9
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Jul 30, 2014
Messages
9
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

New Member
Joined
Jul 30, 2014
Messages
9
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.
 

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top