Change last characters in multiple words in a cell

frnkt68

New member
Joined
Jul 31, 2014
Messages
8
Reaction score
0
Points
0
Excel Version(s)
Excel 365
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 write it correctly.
So I would very much appreciate your help.


Regards
 
So how many rules are there? If lots, a formula solution is going to be difficult to manage.

I agree.
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). Unfortunately, the records do not comply with the grammatical rules of our language and I'd like to fix that. 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 suggestion or solution will be appreciated.

Regards
 
So it would have to be a vba (macro) solution and we'd need the full list of rules… exact rules (remember, we'll be instructing a mindless computer to do the job).
 
So it would have to be a vba (macro) solution and we'd need the full list of rules… exact rules (remember, we'll be instructing a mindless computer to do the job).

I attached the file with rules and some examples.
As I stated in the comments in the file, I need to manually correct the contents of certain cells (all cells containing "." "," ";" "(" ")" "[" "]" and numbers).
 

Attachments

  • test2.xlsx
    13.3 KB · Views: 7
try the following (it's executed by clicking the button on the examples sheet in the attached):
Code:
Sub blah()
Rules = Sheets("Rules").Range("A2:B61")
ubr = UBound(Rules)
For Each cll In Range("A2", Cells(Rows.Count, "A").End(xlUp))
  x = Split(Application.Trim(cll.Value))
  For j = 0 To UBound(x)  'Each wrd In x
    For i = 1 To ubr
      LenSuffix = Len(Rules(i, 1))
      If Right(x(j), LenSuffix) = Rules(i, 1) Then
        x(j) = Left(x(j), Len(x(j)) - LenSuffix) & Rules(i, 2)
        Exit For
      End If
    Next i
  Next j
  cll.Offset(, 2) = Join(x)
Next cll
End Sub
 

Attachments

  • ExcelGuru3322test2.xlsm
    23.5 KB · Views: 19
try the following (it's executed by clicking the button on the examples sheet in the attached):
Code:
Sub blah()
Rules = Sheets("Rules").Range("A2:B61")
ubr = UBound(Rules)
For Each cll In Range("A2", Cells(Rows.Count, "A").End(xlUp))
  x = Split(Application.Trim(cll.Value))
  For j = 0 To UBound(x)  'Each wrd In x
    For i = 1 To ubr
      LenSuffix = Len(Rules(i, 1))
      If Right(x(j), LenSuffix) = Rules(i, 1) Then
        x(j) = Left(x(j), Len(x(j)) - LenSuffix) & Rules(i, 2)
        Exit For
      End If
    Next i
  Next j
  cll.Offset(, 2) = Join(x)
Next cll
End Sub


Thank you, iz works!:biggrin1:




I hope I'm not asking too much, but I have one more request. Would you be so kind and explain to me how this macro works, so I would understand it?



Regards
 
First, select the examples sheet to make sure it's the active sheet, then in the visual basic editor, make sure the Locals window is visible (View|Locals Window), then click somewhere in the macro you want to run, then press F8 repeatedly to step through the code step by step, while doing this, look at the variables in the Locals window (you can click the little + sign to expand them if they are expandable (Rules and x will be), and you will follow what's going on.
 
Well I guess it is now clear that neither my knowledge of Excel nor English is not exactly brilliant.

Apparently I was not clear enough - I meant, if you could explain to me what each line of code does.

This part I understand
Code:
Sub blah()
Rules = Sheets("Rules").Range("A2:B61")
ubr = UBound(Rules)
For Each cll In Range("A2", Cells(Rows.Count, "A").End(xlUp))

Code:
 Exit For
      End If
    Next i
  Next j
  cll.Offset(, 2) = Join(x)
Next cll
End Sub


But this part I do not understand and I will appreciate it if you could explain it to me.

Code:
 x = Split(Application.Trim(cll.Value))
  For j = 0 To UBound(x)  'Each wrd In x
    For i = 1 To ubr
      LenSuffix = Len(Rules(i, 1))
      If Right(x(j), LenSuffix) = Rules(i, 1) Then
        x(j) = Left(x(j), Len(x(j)) - LenSuffix) & Rules(i, 2)
 
Your English is OK, and I understood perfectly, only I find adding verbose comments is too much hard work and guided you instead to where you could see for yourself what was happening in the code.

If you watch x in the Locals window when you meet the line x= split… for the first time you'll see that x changes from empty to become an array (mostly this array will only have one value (x(0)) but sometimes, as when row 7 is encountered where there are 3 words, x will have 3 members: x(0 to 2). In this last case Ubound(x) is 3 so the loop will loop 3 times, once for each word. You can see these words if, as I suggested earlier, you click on the small + sign to expand x to make its members visible in the Locals window. [You can do the same with Rules where you can see the current and future endings of words.]
LenSuffix is a variable name I invented to make a note of the length of the ending of the word you're looking to change (this is mostly 2 but just once it's 3 where the ending you're looking for is 'ept'). I've put it into a variable because I'm going to use it more than once in the loop; 1. to compare the correct number of letters at the end of each word and 2. to know how many letters to remove at the end of the word before replacing them with the correct version.
The Exit For is to stop looking (stop looping) for further endings after the first correction has been found - if we didn't do this then you would get into the situation where, for example, a word initially ending 'ak' gets changed to 'aka' and this new word might get changed a second time from ending in 'ka' to ending in 'ke'.

You can see that the above paragraph's length far exceeds the 2 or 3 lines of code that it's describing.

If I can show you how to find out for yourself how things are working, I believe that is more valuable than explaining one specific piece of code.

So a couple more things you can do while stepping through the code:
Hover with the mouse over items such as the x in
x(j)
and you'll see a popup showing its value. Hover over the j and you'll see the value of j. Hover over
Len(x(j))
and you'll see the length of x(j) pop up. Hover over the word Rules where you have
Rules(i, 1)
or
Rules(i, 2)
and you'll see their values.

If you're unsure about whether something is a function or not, click in it so that the cursor is somewhere in the word and press F1, if it is a function/method you'll get a help topic, if it's unrecognised you'll get something like 'keyword not found'. This is useful when trying to understand, for example, Exit For, click somewhere in the word Exit, press F1 and you'll get a help topic which includes Exit For and saves me from having to type it out myself.

In the Immediate window (bring it up with Ctrl+G if it's not visible) you can get values which may not have been assigned to variables, for example UBound(x) in For j = 0 To UBound(x), to find out what that value is you can type:
?UBound(x)
in the Immediate window and press Enter and it will show you what that value is.
 
Last edited:
Thank you very much for the detailed explanation. :hippie:
 
Back
Top