Strings

[Craig]

In an earlier post, I said that I wanted to eliminate
funky characters (i.e. /, \, }, {, *, #, etc.) from part
numbers, thereby turning N-11#4 into N114. I want to be
able to specify which characters I am going to eliminate.
These could include characters or substrings, like
demonstrated below.

I have gotten code that will do that, but not specifically
what I need it to do....i need something that is really
like a replace function, but I am using Access 97.

in addition to the funky characters listed above, i need
to do the following...

turn: N-11(OLD)
into: N11

turn: 12-34#5(FINE)
into: 12345

turn: 1 2 345(NEW)
into: 12345

the code i have is the following, but does not account for
the (NEW), (OLD), (FINE) stuff. can anyone help?



Function Cleanse(ByVal InString As String) As String

Dim StringLength As Long
Dim OutString As String
Dim i As Long
Dim TempString As String

StringLength = Len(InString)
OutString = ""

For i = 1 To StringLength
TempString = Left$(InString, 1)
InString = Right$(InString, StringLength - i)

If (Asc(TempString) >= 65 And Asc(TempString) <= 90) Or _
(Asc(TempString) >= 97 And Asc(TempString) <= 122) Or _
(Asc(TempString) >= 48 And Asc(TempString) <= 57) Then

OutString = OutString & TempString
End If

Next i

Cleanse = OutString

End Function

 

[JSand42737]

In an earlier post, I said that I wanted to eliminate
funky characters (i.e. /, \, }, {, *, #, etc.) from part
numbers, thereby turning N-11#4 into N114. I want to be
able to specify which characters I am going to eliminate.
These could include characters or substrings, like
demonstrated below.

I have gotten code that will do that, but not specifically
what I need it to do....i need something that is really
like a replace function, but I am using Access 97.

in addition to the funky characters listed above, i need
to do the following...

turn: N-11(OLD)
into: N11

turn: 12-34#5(FINE)
into: 12345

turn: 1 2 345(NEW)
into: 12345

the code i have is the following, but does not account for
the (NEW), (OLD), (FINE) stuff. can anyone help?



Function Cleanse(ByVal InString As String) As String

Dim StringLength As Long
Dim OutString As String
Dim i As Long
Dim TempString As String

StringLength = Len(InString)
OutString = ""

For i = 1 To StringLength
TempString = Left$(InString, 1)
InString = Right$(InString, StringLength - i)

If (Asc(TempString) >= 65 And Asc(TempString) <= 90) Or _
(Asc(TempString) >= 97 And Asc(TempString) <= 122) Or _
(Asc(TempString) >= 48 And Asc(TempString) <= 57) Then

OutString = OutString & TempString
End If

Next i

Cleanse = OutString

End Function

Craig

If the (NEW), (OLD) etc are always at the end of the string, then you can use
something like this to remove them:

Dim strValue As String
If Instr(strValue,"(")>0 Then
strValue=Left(strValue,InStr(strValue,"(")-1)
End If

 

[Marshall Barton]

In an earlier post, I said that I wanted to eliminate
funky characters (i.e. /, \, }, {, *, #, etc.) from part
numbers, thereby turning N-11#4 into N114. I want to be
able to specify which characters I am going to eliminate.
These could include characters or substrings, like
demonstrated below.

I have gotten code that will do that, but not specifically
what I need it to do....i need something that is really
like a replace function, but I am using Access 97.

in addition to the funky characters listed above, i need
to do the following...

turn: N-11(OLD)
into: N11

turn: 12-34#5(FINE)
into: 12345

turn: 1 2 345(NEW)
into: 12345

the code i have is the following, but does not account for
the (NEW), (OLD), (FINE) stuff. can anyone help?



Function Cleanse(ByVal InString As String) As String

Dim StringLength As Long
Dim OutString As String
Dim i As Long
Dim TempString As String

StringLength = Len(InString)
OutString = ""

For i = 1 To StringLength
TempString = Left$(InString, 1)
InString = Right$(InString, StringLength - i)

If (Asc(TempString) >= 65 And Asc(TempString) <= 90) Or _
(Asc(TempString) >= 97 And Asc(TempString) <= 122) Or _
(Asc(TempString) >= 48 And Asc(TempString) <= 57) Then

OutString = OutString & TempString
End If

Next i

Cleanse = OutString

End Function

It's starting to look like you only want to keep decimal
digits and throw away everything else. If so, you could use

Function Cleanse(ByVal InString As String) As String
Dim i As Long
For i = 1 To Len(InString)
If Mid(InString, i, 1) Like "#" Then
Cleanse = Cleanse & Mid(InString, i, 1)
End If
Next
End Function

If you have some other requirements, please post them in
more detail.

 

[Dirk Goldgar]

In an earlier post, I said that I wanted to eliminate
funky characters (i.e. /, \, }, {, *, #, etc.) from part
numbers, thereby turning N-11#4 into N114. I want to be
able to specify which characters I am going to eliminate.
These could include characters or substrings, like
demonstrated below.

I have gotten code that will do that, but not specifically
what I need it to do....i need something that is really
like a replace function, but I am using Access 97.

in addition to the funky characters listed above, i need
to do the following...

turn: N-11(OLD)
into: N11

turn: 12-34#5(FINE)
into: 12345

turn: 1 2 345(NEW)
into: 12345

the code i have is the following, but does not account for
the (NEW), (OLD), (FINE) stuff. can anyone help?



Function Cleanse(ByVal InString As String) As String

Dim StringLength As Long
Dim OutString As String
Dim i As Long
Dim TempString As String

StringLength = Len(InString)
OutString = ""

For i = 1 To StringLength
TempString = Left$(InString, 1)
InString = Right$(InString, StringLength - i)

If (Asc(TempString) >= 65 And Asc(TempString) <= 90) Or _
(Asc(TempString) >= 97 And Asc(TempString) <= 122) Or _
(Asc(TempString) >= 48 And Asc(TempString) <= 57) Then

OutString = OutString & TempString
End If

Next i

Cleanse = OutString

End Function

I already replied to one of your many earlier posts, addressing this
question. I see no sign that you've read my response. Perhaps if you
didn't post so many independent messages, but instead followed up in the
original thread, you would arrive at a satisfactory solution sooner.
Don't start so many hares; you can't chase them all.