Posted By

warwick-datum-process on 06/28/12


Tagged

case conversion access vba Microsoft


Versions (?)

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


 / Published in: Visual Basic
 

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".

  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  

You need to login to post a comment.