Removing items from string

[apartain]

I am trying to concatenate several fields, but need to remove spaces, open and close parentheses and periods.

For example, the field InspectionType holds the following choices from a drop-down:

Annual
Load Test
P.M. (03 Month)
P.M. (06 Month)
P.M. (09 Month)
P.M. (12 Month)
Wire Rope
Repair

I need them to appear like this:

annual
loadtest
pm03
pm06
pm09
pm12
wirerope
repair

[Killer42]

I am trying to concatenate several fields, but need to remove spaces, open and close parentheses and periods.

For example, the field InspectionType holds the following choices from a drop-down:

Annual
Load Test
P.M. (03 Month)
P.M. (06 Month)
P.M. (09 Month)
P.M. (12 Month)
Wire Rope
Repair

I need them to appear like this:

annual
loadtest
pm03
pm06
pm09
pm12
wirerope
repair

You also removed the word "Month" - was this intended? It's likely to make a big difference if we write code to do the change.

[Killer42]

Here's a quick and simple (and minimally tested) function to return a crunched version of a string. To try it out, just paste the whole thing into a fresh code module.

Option Explicit
Private Const CharsToRemove As String = " ()."

Public Function CrunchedText(ByVal srcText As String) As String

  Dim L As Long, I As Long, Char As String
  Dim TempText As String
  L = Len(srcText)
  If L = 0 Then Exit Function
  
  For I = 1 To L
    Char = Mid$(srcText, I, 1)
    If InStr(CharsToRemove, Char) Then
      ' Do nothing
    Else
      ' Add character to output string.
      TempText = TempText & Char
    End If
  Next
  CrunchedText = TempText
End Function

Note though, as mentioned in my prior post, if you need specific words removed it will make quite a difference to this code. Also, if you need lowercase conversion you can just add it, either in the Function or after (or before) calling it.

[ADezii]

I am trying to concatenate several fields, but need to remove spaces, open and close parentheses and periods.

For example, the field InspectionType holds the following choices from a drop-down:

Annual
Load Test
P.M. (03 Month)
P.M. (06 Month)
P.M. (09 Month)
P.M. (12 Month)
Wire Rope
Repair

I need them to appear like this:

annual
loadtest
pm03
pm06
pm09
pm12
wirerope
repair

There are several approaches to solving your problem, but I think that this is one of the simpler ones requiring a minimal amount of code. This Function will:
1) Accept a String Argument and return the same String void of any of the following characters ==> (). " " (space)
2) Convert the returned String to Lower Case.
3) Remove any occurrance of the word 'Month' at the end of the String.
4) e.g. Dim Response As String
Response = fRemoveItems("( .. ..() W. .(((o )()(..W.. )!") returns wow!
Response = fRemoveItems("P .M. (12 Month)") returns pm12
5) Any further questions on ways to implement this Function, please feel free to ask.

'Function calls:
Dim Response As String
Response = fRemoveItems("(..  ..() W.   .(((o )()(..W.. )!")
Response = fRemoveItems("P.M. (12 Month)")

Public Function fRemoveItems(MyString As String) As String
Dim intLength As Integer, intCounter As Integer, strLetter As String
Dim strValidChar As String, strStrippedString As String

If Len(MyString) = 0 Then Exit Function

intLength = Len(MyString)

For intCounter = 1 To intLength
  strLetter = Mid(MyString, intCounter, 1)
  If InStr("().", strLetter) > 0 Or strLetter = " " Then
    'do nothing
  Else
    strStrippedString = strStrippedString & strLetter
  End If
Next intCounter

If InStr(strStrippedString, "Month") > 0 Then
  fRemoveItems = LCase$(Left$(strStrippedString, InStr(strStrippedString, "Month") - 1))
Else
  fRemoveItems = LCase$(strStrippedString)
End If
End Function

[ADezii]

Here's a quick and simple (and minimally tested) function to return a crunched version of a string. To try it out, just paste the whole thing into a fresh code module.

Option Explicit
Private Const CharsToRemove As String = " ()."

Public Function CrunchedText(ByVal srcText As String) As String

  Dim L As Long, I As Long, Char As String
  Dim TempText As String
  L = Len(srcText)
  If L = 0 Then Exit Function
  
  For I = 1 To L
    Char = Mid$(srcText, I, 1)
    If InStr(CharsToRemove, Char) Then
      ' Do nothing
    Else
      ' Add character to output string.
      TempText = TempText & Char
    End If
  Next
  CrunchedText = TempText
End Function

Note though, as mentioned in my prior post, if you need specific words removed it will make quite a difference to this code. Also, if you need lowercase conversion you can just add it, either in the Function or after (or before) calling it.

Didn't mean to step on your toes. Just thought I took the time to write the code, I may as well post it. After all, it's not 'exactly' the same.

[Killer42]

Didn't mean to step on your toes. Just thought I took the time to write the code, I may as well post it. After all, it's not 'exactly' the same.

No toe-stepping taken. :)

It's always good to get different people's perspectives on things. As you can see, to take one example I was very lazy with my variable names (Eg. I, L).

(Also, I figured I wouldn't bother to put in the handling of "Month" until getting more detailed specs.)

[George Oro]

You can use this also, paste this code to a new module and named as you like.

'This function replace the built-in Replace function:
Function FindAndReplace(ByVal strInString As String, _
        strFindString As String, _
        strReplaceString As String) As String
Dim intPtr As Integer

    
    If Len(strFindString) > 0 Then  'catch if try to find empty string
        Do
            intPtr = InStr(strInString, strFindString)
            If intPtr > 0 Then
                FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
                                        strReplaceString
                    strInString = Mid(strInString, intPtr + Len(strFindString))
            End If
        Loop While intPtr > 0
    End If
    FindAndReplace = FindAndReplace & strInString
    
End Function

To Test it:

dim strReturn as string
strReturn =FindAndReplace ("George Oro"," ","")

the above will remove the space and return GeorgeOro

For sure you have loop to your recordset to clean them all...

happy coding
George

I am trying to concatenate several fields, but need to remove spaces, open and close parentheses and periods.

For example, the field InspectionType holds the following choices from a drop-down:

Annual
Load Test
P.M. (03 Month)
P.M. (06 Month)
P.M. (09 Month)
P.M. (12 Month)
Wire Rope
Repair

I need them to appear like this:

annual
loadtest
pm03
pm06
pm09
pm12
wirerope
repair

[ADezii]

No toe-stepping taken. :)

It's always good to get different people's perspectives on things. As you can see, to take one example I was very lazy with my variable names (Eg. I, L).

(Also, I figured I wouldn't bother to put in the handling of "Month" until getting more detailed specs.)

In hindsight, do you feel it would be more efficient to load each character of the String into a String Array, then process each character individually? I'm thinking along along the lines of possibly processing thousands of Strings via a Calculated Field that calls this Function.

[Killer42]

In hindsight, do you feel it would be more efficient to load each character of the String into a String Array, then process each character individually? I'm thinking along along the lines of possibly processing thousands of Strings via a Calculated Field that calls this Function.

There are probably ways we can tweak the code for performance. But as George indirectly pointed out, Access probably has a text search/replace feature you can make use of. I'll check with some Access experts and get back to you.

[Killer42]

There are probably ways we can tweak the code for performance. But as George indirectly pointed out, Access probably has a text search/replace feature you can make use of. I'll check with some Access experts and get back to you.

As it turns out, this code is probably redundant. See this link for info in Access's Replace function, which appears to do exactly the type of search/replace function that we have been discussing. (Thanks go to mmccarthy for this tidbit.)

So, I don't think there's much point trying to tweak the performance of the code. Just replace it with a Replace function call. Actually, a bunch of them I suppose - one per change (replace space with nothing, dot with nothing, etc).