Find and replace characters.

I an attempting to write a script that can find things like "\"," " ", "*", and even ","..., now I need to replace them with either "-" or nothing. Is there a way to do this if so how?

But in editing, I can't remove the cell itself... it will look like this. Nexx Systems, Inc. and will become Nexx Systems Inc

Thanks.

Originally Posted by rvasquez

Okay below is a code that should work for you

'declare variables
Dim Sym As String, ReplSym As String

    'prompts user to input a symbol to search for
    Sym = InputBox(Prompt:="Enter the symbol you would like to replace.", _
          Title:="ENTER SYMBOL")


    'prompts user for replacement symbol or text
    ReplSym = InputBox(Prompt:="Enter what you would like to replace the symbol with.", _
          Title:="ENTER REPLACEMENT STRING")
         

        'if the first input is nothing tell the user they didn't enter anything to search for
        If Sym = vbNullString Then

           Exit Sub
            MsgBox "You did not enter a search value."


        Else
            'else find the cells whose value is equal to what you searched for
            'and replace it with the value you entered in the second input box.
            Cells.Replace what:=Sym, replacement:=ReplSym, lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False


        End If

To enter the code as a macro hold down the Alt key and press F8. In the macro name box provided delete anything in it and then type GetSymbol and select the create option.

In between the

Sub GetSymbol

and the text

End Sub

Copy and paste the above code.

Then exit out of VBA. To run the code select Alt+F8 again and then select the GetSymbol option and select Run.

Let me know if this works for you!

Thanks!

How do I add in multiple items? I know which ones I need replaced? I just need the code to run, I really don't want an input box, I hope you understand but basically I am trying to make this work without the end user knowing it's being done for them.

I found this, but I need to only change what is in S and T, down to the last row of each...

Sub StripCharacters()
    Dim RE As Object
    Dim rng As Range
    Set RE = CreateObject("VBScript.RegExp")
    With RE
        .Global = True
        ' Place the WANTED characters between [^ and ]
        .Pattern = "[^a-zA-Z0-9]"
        For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
            rng.Value = .Replace(rng.Value, "")
        Next rng
    End With
End Sub

The other issue is I need to make this work on a specific sheet, not the entire workbook...

What I'd like it to do is to replace the extra characters with a "-" even the ", so that it can be made into a folder. Basically I have this as a script so far:

With wsJL
    Dim baseFolder As String, newFolder As String
    Dim cell As Range

    lastrow = wsJL.Cells(Rows.Count, "B").End(xlUp).Row

    baseFolder = CleanName(Sheets("Lists").Range("$G$2").Value)      'folders will be created within this folder

    If Right(baseFolder, 1) <> Application.PathSeparator Then baseFolder = baseFolder & Application.PathSeparator

    For Each cell In Range("S3:S" & lastrow)    'CHANGE TO SUIT

        'Company folder - column A

        newFolder = baseFolder & CleanName(cell.Value)
        If Len(Dir(newFolder, vbDirectory)) = 0 Then MkDir newFolder

        'Part number subfolder - column C

        newFolder = newFolder & Application.PathSeparator & CleanName(cell.Offset(0, 1).Value)
        If Len(Dir(newFolder, vbDirectory)) = 0 Then MkDir newFolder

    Next
   End With
End Sub

Function CleanName(strName As String) As String
'will clean part # name so it can be made into valid folder name
'may need to add more lines to get rid of other characters

    CleanName = Replace(strName, "/", "")
    CleanName = Replace(CleanName, "*", "")
    CleanName = Replace(CleanName, ".", "")
    CleanName = Replace(CleanName, ",", "")
    CleanName = Replace(CleanName, """", "")
End Function

But for some reason it's not replacing the " and the , as it should in theory...

Originally Posted by rvasquez

Sub test()

Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        'replace test1,test2 etc. with what you would like to replace the contents with
        c.Value = Replace(c.Value, "…", "test1", 1, 1, vbTextCompare)
        c.Value = Replace(c.Value, "\", "test2", 1, 1, vbTextCompare)
        c.Value = Replace(c.Value, ".", "test3", 1, 1, vbTextCompare)
        c.Value = Replace(c.Value, "*", "test4", 1, 1, vbTextCompare)
        c.Value = Replace(c.Value, ",", "test5", 1, 1, vbTextCompare)
        c.Value = Replace(c.Value, """", "test6", 1, 1, vbTextCompare)
    Next
End Sub

I think you responded before I edited the post. I'll use what you have if nothing else can be done, but I need it for two columns, not the entire sheet.