Stripping spaces and vowels from strings in Access
Q: I heard something interesting at the conference I attended last week that will help me in my daily work but I can't figure out how to do it. In Access, how would one go about removing spaces and vowels from a text string?
A: Get ready for this, because it ain't pretty...
CutVersion: Replace (Replace (Replace (Replace (Replace (Replace ([Field1], "a", ""), "e", ""), "i", ""), "o", ""), "u", ""), " ", "")
That'll do it, but a custom function in VBA would probably be neater.
Postscript: Greg Chapman (http://www.layer1wireless.com) proved me right on this last point, with this follow-up:
You're right, that's ugly but it's very functional. I like it!
Here's the VBA version (no prettier, I'm afraid) with a subroutine made to test the function:
Sub TestReplaceSpaces()
strTest = "This is a test string."
MsgBox strTest
strTest = ReplaceSpacesVowels(strTest)
MsgBox strTest
End Sub
Function ReplaceSpacesVowels(strToSearch)
Dim arrCharacters(11)
arrCharacters(0) = "a"
arrCharacters(1) = "e"
arrCharacters(2) = "i"
arrCharacters(3) = "o"
arrCharacters(4) = "u"
arrCharacters(5) = "A"
arrCharacters(6) = "E"
arrCharacters(7) = "I"
arrCharacters(8) = "O"
arrCharacters(9) = "U"
arrCharacters(10) = " "
For I = 0 To 10
strToSearch = Replace(strToSearch, arrCharacters(I), "") Next I
ReplaceSpacesVowels = strToSearch
End Function