Excel and Office RAQ

Stripping spaces and vowels from strings in Access


Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing


Knowledge measurement audit

Item bank services

Item design course








Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in


Home page

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



Back to other RAQ topics