You can combine the first two blocks of code into one.
I've deleted the unnecessary For each cll loop.
Added a few more arguments to the .Replace line. On that line you should experiment with MatchCase:=True/MatchCase:=False to be sure it gives you what you want.
Be aware that this will initially and unconditionally make the first letter of every word lower case, including the very first word in the string.
Why don't you run this on a whole bunch of cells, then come back here with examples of just those results which aren't quite right, including how they were originally, and how you'd likethem to be.
Sub Main() too = Array("in", "with", "en", "pour", "para", "a", "per", "di", "de", "avec", "contre", "dans", "entre", "par", "sans", "sur", "bis", "für", "aus", "mit", "nach", "von", "auf", "sopra", "tra", "da", "con", "contra", "por", "sin") For Each cll In Selection.Cells xx = Split(cll.Value) For i = LBound(xx) To UBound(xx) xx(i) = LCase(Left(xx(i), 1)) & Mid(xx(i), 2) If IsError(Application.Match(xx(i), too, 0)) Then xx(i) = UCase(Left(xx(i), 1)) & Mid(xx(i), 2) Next i cll.Value = Join(xx) Next cll Selection.Replace What:="dot ", Replacement:="", LookAt:=xlPart, Searchformat:=False, MatchCase:=False End Sub





Reply With Quote