Function cmRemChar(cell1 As Range, char1 As String) Dim instr1 As Integer Dim tmpStr As String tmpStr = cell1.Value instr1 = InStr(1, tmpStr, char1) Do While instr1 > 0 tmpStr = Left(tmpStr, instr1 - 1) & Mid(tmpStr, instr1 + 1) instr1 = InStr(1, tmpStr, char1) Loop cmRemChar = tmpStr End Function
Below is an example of an excel sheet where there are a couple part numbers that have some unwanted characters. In this case a “-”, or minus sign. The goal of the code above will be to remove all of the unwanted characters.
The screen below shows what you will see as you impliment the visual basic function. To start with you are going to need to open the visual basic editor:
Now enter the formula as in the image below to test the operation of the formula.