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...
Bookmarks