CODEWITHSUNDEEP
vba
Sum Guy
Sum Guy

Reputation: 13

MS Access Remove Words from Text

I'm trying to remove various words from a text field in MS Access. The data might look like:

Hi there @foo, what's new @bar @goodfriend and I just watched Star Wars @this and @that and @theother

I want to remove all the words that start with '@'.

Replace() won't work since the words are different in each record.

Any ideas?

Upvotes: 1

Views: 1554

Answers (2)

VBlades
VBlades

Reputation: 2251

I actualy upvoted CheeseInPosition's answer but just thought I would provide another way if you can't/don't want to do it with regex. Just wrote a quick function to parse out the words:

Public Function RemoveAtWords(strOriginal As String) As String

    Dim strParts() As String
    Dim i As Integer

    strParts() = Split(strOriginal, " ")

    For i = LBound(strParts) To UBound(strParts)
        If Left(strParts(i), 1) <> "@" Then
            RemoveAtWords = RemoveAtWords & " " & strParts(i)
        End If
    Next

    RemoveAtWords = Trim(RemoveAtWords)

End Function

You can call that from a query and pass through your string. Not as efficient because you have to loop through the whole string, but just another option.

Upvotes: 0

RedGreenCode
RedGreenCode

Reputation: 2299

If you're using VBA, you should be able to replace text based on regular expressions. See the answer to replace a column using regex in ms access 2010 as an example.

Upvotes: 1

Related Questions