[Office] Transfer Capital Letters in Excel

This could be done through a few code steps. Please follow the instruction below:

Save your Excel File as “Excel Marco-Enabled Workbook (*.xlsm)

·         Add one column to help you to identify which filed has Capital character visibly

·         Type the formula in to the filed (Change your target field, in the case is A2), then apply to the whole column

IF it returns “FALSE”, it means you have UPPER case in the field

                =EXACT(A2,LOWER(A2))

Now we need to write a simple code

·         Press keyboard Alt+F11, it will revoke a code editor called “Microsoft Visual Basic for Applications”

·         Insert a new Module

·         Double click “Module1”, then type the code in the prompt windows below

Sub ToLowerCase()

Const upperCaseList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Dim anyName As Range

Dim LC As Integer

 

Application.ScreenUpdating = False ' runs faster

For Each anyName In Selection

  For LC = 1 To Len(upperCaseList)

    anyName.Value = Replace(anyName.Value, _

     Mid(upperCaseList, LC, 1), LCase(Mid(upperCaseList, LC, 1)))

  Next

Next

MsgBox "Job Completed"

End Sub

Save > Close

How to use it?

·         Select the range that you want to transfer all UPPER case to the lower case

·         Click “VIEW” tab > Click “Marcos”

You will see another prompted windows, make sure the data that you want to transfer

Click “RUN”

·         Click “OK”

·         Wonderful!!

Thanks,

Credits to:

• ExtendOffice: How to identify uppercase, lowercases, and proper cases in Excel? (Link)
• DSLReports: [Excel] Capital Letter Search (Link)
• StackOverFlow: SplitUppercase words in Excel (Link)