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.
Create a new workbook. Open the VBA code window and insert a module.
Paste the following data into the first sheets cell A1.
|USA Armed Forces|
|USA armed forces|
|301 capital House|
Rename this sheet “Main”.
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”
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.
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