How to Standardize the Texts in Your Spreadsheet with Excel VBA

We’ve all seen spreadsheet names and addresses with a mix of formats like JAMES JONES, sARAH bERNARDT or clint eastwood, all of which look amateurish on a report. This article shows how to standardise text in the proper way in a worksheet.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The Excel file can be found here

In this exercise we’ll build a function capable of recognising, to a large extent, the intent of the user. For instance, the Excel proper function would reduce ‘NAFTA’ to ‘Nafta’. In the rare case where someone enters ‘usa armed forces’, the function will not be able to determine the intent, since it has no dictionary of acronyms, and will quite properly change it to ‘Usa Armed Forces’. Some miscreants will thus slip through the net.

What the Function Does

The text is split into two parts, one on each side of the first space.  Therefore ‘USA Armed Forces’ becomes a pre-string consisting of ‘USA’, and a post-string of ‘Armed Forces’.

• If there is at least one lower case character in the post-string, then it’s a reasonable assumption that the caps lock wasn’t accidently left on during capture. The post-string will thus be formatted to the proper case.
• If the second character of the prestring is upper case, then we can reasonably assume that the entire pre-string should be capitalised.

The Data

Create a new workbook. Open the VBA code window and insert a module.

Paste the following data into the first sheets cell A1.

Organisation	Proper Case
USA Armed Forces
USA armed forces
UNESCO committee
bARRY JONES
BarRy JONES
1213456
alan courtney
NAFTA
301 capital House

Rename this sheet “Main”.

The Code.

Copy and paste the following into the VBA code window of the workbook:

Option Explicit
     Dim strText As String
     Dim preString As String
     Dim postString As String
     Dim uCount As String
     Dim lCount As String
     Dim b As Integer
     Dim i As Integer
     Dim char2 As String

Sub Main()
     Dim strText As String
     Dim cRow As Integer 'Current row
     cRow = 2
     Sheets("Main").Select
     Range("A2").Select

     Do While ActiveCell > ""
         strText = ActiveCell
         strText = fProper(strText)
         Cells(cRow, 2) = strText
         cRow = cRow + 1
         Cells(cRow, 1).Select
     Loop
 
End Sub


Function fProper(strTxt As String)
     strText = strTxt
     uCount = 0
     lCount = 0
 
     'Seek the first space.
     b = InStr(1, strText, " ")
 
     'Test if there IS a space
     If b > 0 Then
         preString = Left(strText, b - 1)
         postString = Mid(strText, b, (Len(strText) - b) + 1)
 
         'Cycle through the post-string;
         'at least 1 lower case character will imply that the caps lock wasn't on
         For i = 1 To Len(postString)
             Select Case Asc(Mid(postString, i, 1))
                 Case 65 To 90
                     uCount = uCount + 1
                 Case 97 To 122
                     lCount = lCount + 1
                 Case Else
            End Select
            If lCount > 0 Then Exit For 'Go no further if a lowercase character is found
        Next i
 
        If lCount > 0 Then
            postString = StrConv(postString, 3) '3=proper case, 2=lowercase, 1=upper case
 
            'If the 2nd character of the pre-string is uppercase, it is reasonable
            'to assume the entire pre-string should be too.
            char2 = Mid(preString, 2, 1)
            If Asc(char2) >= 65 And Asc(char2) <= 90 Then
                preString = StrConv(preString, 1) 'entire pre-string is upper
            Else
                preString = StrConv(preString, 3) 'pre-string is proper
            End If
        Else
            preString = StrConv(preString, 3) 'No lower case found, Caps Lock stuck;
            postString = StrConv(postString, 3) 'Reduce the entire string to proper
        End If
        fProper = preString & postString 'Add the two elements together
     Else
 
         'No space was found, a reasonable assumption as to case can't be made;.
         'pass the string back unaltered.
         fProper = strText
     End If
End Function

Add a button to Sheet “Main” and assign it to sub procedure “Main”

The Results

Press the button, then examine column B of the sheet for the results.

Recovering damaged workbooks

From time to time Excel, like most software, will crash. The normal recovery routines that Excel would use to recover the workbook will fail more often than not. At such times it would be useful to have a tool to do Excel recovery for xlsx or xlsm files.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com