Advanced Search

Splitting the words in a TitleCase string without using regular expressions (for VBA)

/ Published in: Visual Basic
Save to your folder(s)
Using VBA (Visual Basic for Applications) in MS Access, I needed to convert TitleCase to lower\_case, but did not have the regex enqine that's available in VB Script. This solution, based on a simple state machine model, splits the words in the string (converting to lower-case is trivial after that). It treats a block of upper-case letters as a single word, but the last letter of that block is the first letter of the next word; e.g., "VBAIsLame" becomes "VBA Is Lame". Because of this behaviour, if you subsequently convert to lower-case, you cannot reliably convert back to the original (all-upper-case words are indistinguishable from title-case words). To resolve this potential ambiguity, you can supply an "upper\_case\_indicator" that will be inserted before each capital letter that isn't the start of a word. So `TitleCaseSplit("VBAIsLame", "_", "-")` returns "V-B-A\_Is\_Lame".

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Private Function TitleCaseSplit(str As String, Optional delim As String = " ", Optional upper_case_indicator As String = "") As String
  2.     Dim chr As String, out As String
  3.     Dim state As String
  4.     Dim i As Integer
  5.     Dim is_upper As Boolean
  6.     chr = Mid(str, 1, 1)
  7.     out = chr
  8.     state = "first character"
  9.     For i = 2 To Len(str)
  10.         chr = Mid(str, i, 1)
  11.         is_upper = StrComp(chr, LCase(chr), vbBinaryCompare)
  12.         Select Case state
  13.             Case "first character"
  14.                 If is_upper Then
  15.                     state = "upper-case word"
  16.                 Else
  17.                     state = "title-case word"
  18.                 End If
  19.                 out = out & chr
  20.             Case "title-case word"
  21.                 If is_upper Then
  22.                     state = "first character"
  23.                     out = out & delim & chr
  24.                 Else
  25.                     out = out & chr
  26.                 End If
  27.             Case "upper-case word"
  28.                 If is_upper Then
  29.                     out = Left(out, Len(out) - 1) & upper_case_indicator & Right(out, 1) & chr
  30.                 Else
  31.                     state = "title-case word"
  32.                     out = Left(out, Len(out) - 1) & delim & Right(out, 1) & chr
  33.                 End If
  34.         End Select
  35.     Next
  36.     TitleCaseSplit = out
  37. End Function

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.