Hi. A workbook has several sheets. Columns E,F and G of 14 sheets contain LastName, FirstName and MiddleName all in upper cases. In col T (FullName) I joined cols E,F,G but in proper case. I merge all 14 sheets by power query, re arranging the position of the FullName, it is no longer in column T.
My problem is that the letters right after the hyphens are capitalized by the PROPER function in col T which is wrong. (i.e from AW-AN it was changed to Aw-An when it should be Aw-an). Everytime I refresh the merge, I perform the find all the capital letters with a hyphen before them, then replace with the small letter which is time consuming. My search led me to this vba but when I ran it, nothing changed.
Sub VBA_Replace()
Str1 = "-A"
Str1 = Replace (str1, "-A", "-a")
Str2 = "-E"
Str2 = Replace (str2, "-E", "-e")
Str3 = "-I"
Str3 = Replace (str3, "-I", "-i")
..... until O and U
End Sub
I would like the above code to find all capital letters after a hypen in column FullName then replace them to small letters.
Kindly help, thank you.
Also, there are name extensions like II and III but only 3 cases so I just manually change them from Ii and Iii to II and III. BTW they are not descendants of royalty and the pope.
Internet signal here is so weak, I couldn't use the laptop to attach a sample.