User Tools

Site Tools


faq:remove_character_excel_routine

Remove Character Excel Routine

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:

  • In excel go to the tools menu > Macro > Visual Basic Editor
  • The shortcut for this is ALT+F11
  • The screenshot below will apear, less the Module1 and the Function code for Function cmRemChar.
  • You will need to right click in the project window and choose Insert > Module.
  • This will bring up the Modules section in the project viewer and add Module1 to the view.
  • Paste the code from the code window above into the blank pane on the right. Your screen should now look very familiar to the screenshot below.

Now enter the formula as in the image below to test the operation of the formula.

faq/remove_character_excel_routine.txt · Last modified: 2010/06/09 16:44 by andrew