Eliminating Repeated Characters from a String

[mcarlin]

Hi -

Given a string which contains spaces and special characters - i.e. @, #, $, %, ^, ., etc. - I want to convert the string from, say, "TEMP. HOT WATER #2" to "TEMP_HOT_WATER_2", for example. Essentially, I want to replace all spaces and special characters with "_". However, I do not want two underscores in a row. The code so far goes something like:

    Title1 = Forms!Search!SearchResults.Column(2)
    NullTitle1 = IsNull(Title1)    

    If NullTitle1 = True Then
        Title1 = ""
    Else
        'Replace special characters
        Title1 = Replace(Title1, "-", "_")
        Title1 = Replace(Title1, "&", "_")
        Title1 = Replace(Title1, " ", "_")
        Title1 = Replace(Title1, "#", "_")
        Title1 = Replace(Title1, ".", "_")
        ...
        etc.
   End If

Following this section, I'd like to go through the adjusted string and remove any extra underscores that may exist (i.e. if a space lies next to a special character, two underscores will be inserted as replacements).

Is there a way to access specific characters within a string so as to remove any duplicate adjacent characters? I've tried using a For...Next loop along with some combinations of Right(Left(xxx), x) statements, but it all seems so convoluted.

Any ideas? Thanks in advance!

 

[workmad3]

heres a function that will go through and replace all the spaces in a string with an _, as well as chopping multiple spaces down into one space

Private Function Stringreplace(expr As String) As String
MsgBox expr
If Not InStr(1, expr, " ") = 0 Then
Dim i As Integer
Stringreplace = Trim(Left(expr, InStr(1, expr, " "))) & "_" & Stringreplace(Trim(Right(expr, Len(expr) - InStr(1, expr, " "))))
Else
Stringreplace = expr
End If
End Function

as you can see, in cases like this, recursion is your friend

 

[mcarlin]

Ha! Alot shorter than I was going for - thanks!

 

[MrTibbs]

I liked the solution but had a slightly different problem so generalised it ...

Using Access '97 on Win2K
My problem was just to remove all instances of character $ from a string

Sub testsub

Dim strFormula As String

Windows("TempManagementHours.xls").Activate
Sheets("Sheet1").Select '
Range("a2").Select
ActiveCell.End(xlToRight).Select
ActiveCell.End(xlDown).Select ' bottom right

strFormula = "=sheet1!" & ActiveCell.Address

Sheets("StandardLayout").Select

' now find and compare to bottom right corner
Range("b70").Select
While ActiveCell.Value <> "Total Shopfloor Hours"
ActiveCell.Offset(1, 0).Select
Wend
' now on intended row
' skip across to last column
ActiveCell.Offset(0, 5).Select ' skip hidden columns
ActiveCell.End(xlToRight).Select
strFormula = strFormula & " - " & ActiveCell.Address

' result is something like
' =Sheet1!$AT$81 - $AV$86
' wanted
'=Sheet1!AT81 - AV86 for subsequent fill right

strFormula = StringReplace(strFormula, "$", "")

MsgBox strFormula

ActiveCell.Offset(1, 0).Formula = strFormula

End Sub

Private Function StringReplace(expr As String, findStr As String, repStr As String)
' Caution - recursive function

Dim i As Integer
Dim junkStr As String
If Not InStr(1, expr, findStr) = 0 Then
StringReplace = Left(expr, (InStr(1, expr, findStr)) - 1) & repStr & StringReplace(Right(expr, Len(expr) - InStr(1, expr, findStr)), findStr, repStr)
Else
StringReplace = expr
End If
End Function

 

[FurryAnimal]

or Another option is

Function Change_Text(ByVal strText As String, ByVal OldCharacter As String, ByVal NewCharacter As String)
While InStr(1, strText, OldCharacter) > 0
strText = Left(strText, InStr(1, strText, OldCharacter) - 1) & NewCharacter & Right(strText, Len(strText) - InStr(1, strText, OldCharacter))
Wend
Change_Text = strText
End Function

very similar to Mrtibbs (thanks for the idea, liked the original) but takes away function recursion.

 

[MrTibbs]

My thanks to Workmad3 for suggesting recursion. I just generalised the solution to work with variable length find and replace strings (I used it to replace "$" with ""). How may your 'while based' function be generalised to support variable length strings?