String Manipulation

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I am trying to do some string manipulation in Access.
This is what I want to do...I want to loop through each
cell and change a part number that looks like: N444-4/8*9
to something that looks like: N444489

in other words, i want to go through and substitute any
specified characters (such as /, -, *, -, etc.) to ""

what would the syntax look like to go through a string and
subtitute characters like the way i have described? any
help would be appreciated. thank you!!
 
Craig said:
I am trying to do some string manipulation in Access.
This is what I want to do...I want to loop through each
cell and change a part number that looks like: N444-4/8*9
to something that looks like: N444489

in other words, i want to go through and substitute any
specified characters (such as /, -, *, -, etc.) to ""

what would the syntax look like to go through a string and
subtitute characters like the way i have described? any
help would be appreciated. thank you!!
Click to expand...

If you don't want to use an extra library, you can also do
this with repeated use of the Replace function.

For a fixed list of characters:
Replace(Replace(Replace(oldpart,"/",""),"-",""),"*","")

Or, for a string containing the list of characters, you
could use Replace in a loop:

Public Function FixPartNum(strOldPartNum As String)
Dim strChars As String
Dim K As Integer
strChars = " /-*"
FixPartNum = strOldPartNum
For K = 1 to Len(strChars)
FixPartNum = Replace(FixPartNum, Mid(strChars, K, 1), "")
Next
End Function
 
Back
Top