By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Removing items from string

P: 58
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
Jan 10 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 5K+
P: 8,434
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.
Jan 11 '07 #2

Expert 5K+
P: 8,434
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.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private Const CharsToRemove As String = " ()."
  3.  
  4. Public Function CrunchedText(ByVal srcText As String) As String
  5.  
  6.   Dim L As Long, I As Long, Char As String
  7.   Dim TempText As String
  8.   L = Len(srcText)
  9.   If L = 0 Then Exit Function
  10.  
  11.   For I = 1 To L
  12.     Char = Mid$(srcText, I, 1)
  13.     If InStr(CharsToRemove, Char) Then
  14.       ' Do nothing
  15.     Else
  16.       ' Add character to output string.
  17.       TempText = TempText & Char
  18.     End If
  19.   Next
  20.   CrunchedText = TempText
  21. 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.
Jan 11 '07 #3

ADezii
Expert 5K+
P: 8,636
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.

Expand|Select|Wrap|Line Numbers
  1. 'Function calls:
  2. Dim Response As String
  3. Response = fRemoveItems("(..  ..() W.   .(((o )()(..W.. )!")
  4. Response = fRemoveItems("P.M. (12 Month)")
Expand|Select|Wrap|Line Numbers
  1. Public Function fRemoveItems(MyString As String) As String
  2. Dim intLength As Integer, intCounter As Integer, strLetter As String
  3. Dim strValidChar As String, strStrippedString As String
  4.  
  5. If Len(MyString) = 0 Then Exit Function
  6.  
  7. intLength = Len(MyString)
  8.  
  9. For intCounter = 1 To intLength
  10.   strLetter = Mid(MyString, intCounter, 1)
  11.   If InStr("().", strLetter) > 0 Or strLetter = " " Then
  12.     'do nothing
  13.   Else
  14.     strStrippedString = strStrippedString & strLetter
  15.   End If
  16. Next intCounter
  17.  
  18. If InStr(strStrippedString, "Month") > 0 Then
  19.   fRemoveItems = LCase$(Left$(strStrippedString, InStr(strStrippedString, "Month") - 1))
  20. Else
  21.   fRemoveItems = LCase$(strStrippedString)
  22. End If
  23. End Function
Jan 11 '07 #4

ADezii
Expert 5K+
P: 8,636
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.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private Const CharsToRemove As String = " ()."
  3.  
  4. Public Function CrunchedText(ByVal srcText As String) As String
  5.  
  6.   Dim L As Long, I As Long, Char As String
  7.   Dim TempText As String
  8.   L = Len(srcText)
  9.   If L = 0 Then Exit Function
  10.  
  11.   For I = 1 To L
  12.     Char = Mid$(srcText, I, 1)
  13.     If InStr(CharsToRemove, Char) Then
  14.       ' Do nothing
  15.     Else
  16.       ' Add character to output string.
  17.       TempText = TempText & Char
  18.     End If
  19.   Next
  20.   CrunchedText = TempText
  21. 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.
Jan 11 '07 #5

Expert 5K+
P: 8,434
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.)
Jan 11 '07 #6

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

Expand|Select|Wrap|Line Numbers
  1. 'This function replace the built-in Replace function:
  2. Function FindAndReplace(ByVal strInString As String, _
  3.         strFindString As String, _
  4.         strReplaceString As String) As String
  5. Dim intPtr As Integer
  6.  
  7.  
  8.     If Len(strFindString) > 0 Then  'catch if try to find empty string
  9.         Do
  10.             intPtr = InStr(strInString, strFindString)
  11.             If intPtr > 0 Then
  12.                 FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
  13.                                         strReplaceString
  14.                     strInString = Mid(strInString, intPtr + Len(strFindString))
  15.             End If
  16.         Loop While intPtr > 0
  17.     End If
  18.     FindAndReplace = FindAndReplace & strInString
  19.  
  20. End Function
To Test it:

Expand|Select|Wrap|Line Numbers
  1. dim strReturn as string
  2. 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
Jan 11 '07 #7

ADezii
Expert 5K+
P: 8,636
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.
Jan 11 '07 #8

Expert 5K+
P: 8,434
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.
Jan 11 '07 #9

Expert 5K+
P: 8,434
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).
Jan 12 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.