Home > database >  How to cycle through a string name and change illegal characters in VB
How to cycle through a string name and change illegal characters in VB

Time:12-16

I'm trying to take in a batch name typed by someone and run it through a loop to replace and illegal characters (!£$%...etc) with "_". Here's what I have:

'define illegal chars
    illegalChars = "!,£,$,%,^,&,*"
'split illegal chars into an array
    illegalCharsArr = Split(illegalChars, ",")
    
'take entered data from textbox
    batch_name = batchEntryBox.Text
    
'split entered data into an array
    batchNameArr = Split(batch_name, " ")
    For Each item In batchNameArr
        For Each i In illegalCharsArr
            If batchNameArr(item) = illegalCharsArr(i) Then
                batchNameEdit = batchNameArr.Replace(batchNameArr(item), illegalCharsArr(i))
            End If
        Next
    Next
    
'post new batch name in Label box
    Label1.Caption = batchNameEdit
End Sub

I can't get it to work. Also, how do i split a name that has no spaces?

Thanks!

CodePudding user response:

Try this logic.

Sub CleanTheWord()

    illegalChars = "!,£,$,%,^,&,*"
    Dim illegalCharsArr() As String
    illegalCharsArr = Split(illegalChars, ",")
    Dim batch_name As String
    batch_name = "£sadg$u^su&bc!ksj*"  'Changed this for testing. Modify accordingly
    
    Dim newstring As String
    newstring = batch_name
    Dim pos As String
    For j = 1 to Len(batch_name)
         pos = Mid(batch_name, j, 1)
         For Each i In illegalCharsArr
            If StrComp(pos, i, vbTextCompare) = 0 Then
                newstring = VBA.Replace(newstring, pos, "_", 1, 1, vbTextCompare)
            End If
        Next
    Next

    MsgBox newstring, vbInformation
    
End Sub

Tried & Tested:

enter image description here

  • Related