Windward Software Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Remove Character Excel Routine ======


<code>
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
</code>

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.

{{faq:01-exampleexcel.jpg|}}


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.


{{faq:02-macroinvbeditor.jpg|}}



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


{{faq:03-formulaexample.jpg|}}