Excel and Office RAQ

Stripping spaces and vowels from strings in Access

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

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