[Office] Transfer Capital Letters in Excel

2015-09-25

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)





Newer Older