Hi,

Pleased to ask a question and hoping for some help.

I am a very noob user, who has been trying to crack this issue for a while with no success.

I am trying to write a code in VBA in Excel and I have two goals in the following order (oh, well - it's one goal, but my nooby mind suggests two steps):
1. To change/keep the first letter of each word in the string as uppercase, while all the rest is left intact.
2. Once the first item is done, then to change the first letter of specific words into lowercase and to completely delete specific, unwanted words.

I have been dealing with the first time for long. Myself:

a. I have managed to change all the first letter to capitals, but, at the same time, all the rest was changed to small letters (which does not help me at all).
b. I have managed to change the case of all the first letters to the opposite. If it was lowercase, then it changed to uppercase (success!). However, if it was already in uppercase, then it changed to lowercase (duh, failure!).

As such, would anyone be able to suggest a code, which changes/keeps the first letter as uppercase and keeps all other letters intact?

For the second item, I already have a solution, which has been running smoothly for me. I created a simple code where:
a. I made a list with all my source words (with the first letter as uppercase) that I want to substitute,
b. I made a list with all my target words (with the first letter as lowercase).

This is what I have been using for the item two:
Sub Prepositions()
    from = Array("In", "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", "Dot", "Dot ", "dot", "dot ")
    too = Array("in", "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 i = LBound(from) To UBound(from)
        Cells.Replace What:=from(i), Replacement:=too(i)
    Next i
End Sub
Lastly, I am happy to illustrate you my issue.


Here is the sample source string in Excel and the target string that I want to achieve after running my sub in Excel:

other33.jpg


I hope I have described my issue clearly and I am keenly looking forward to some pieces of advice on my issue #1, which has been blocking me for a while.

Thank you!