Delete 'n' characters from a range of cells in MS Excel

This post contains VBA macro examples on how to manipulate text strings using the Left, Right and Len functions in Microsoft Visual Basic for Applications in Microsoft Excel to delete some part of the text strings. If you are new to the VBA macros in MS Excel, follow these instructions:

1. Create a new or blank Excel workbook.
2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
3. Go to 'Insert' in the Menu --> Select 'Module'.
4. Copy and Paste any or all of the following macro in the new module sheet.

Sub Delete_Text_From_Left(rng as Range, delChars as Byte)
  'For example: Deleting left 2 characters will turn CLASS into ASS.
  Dim myCell as Range
     On Error Resume Next
     For Each myCell in rng.Cells
        'Ignore if cell length < number of characters to be deleted.
        If Len(myCell.Value) > delChars Then
          'Take the rightmost characters and delete from left.
          myCell.Value = Right(mycell.Value, len(myCell.Value)-delChars)
        End If
     Next
     Set myCell = Nothing
     On Error GoTo 0
End Sub

---

Sub Delete_Text_From_Right(rng as Range, delChars as Byte)
'For example:Deleting 3 chars. from right will turn HEROINE into HERO.
  Dim myCell as Range
     On Error Resume Next
     For Each myCell in rng.Cells
        'Ignore if cell length < number of characters to be deleted.
        If Len(myCell.Value) > delChars Then
           'Keep the leftmost characters and delete from right.
           myCell.Value = Left(mycell.Value, len(myCell.Value)-delChars)
        End If
     Next
     Set myCell = Nothing
     On Error GoTo 0 
End Sub

---

Sub Delete_Txt_From_Middle(rng as Range,startPos as Byte,endPos as Byte)
'For Example: Deleting 2nd & 3rd character from CLASS will make it CSS.
  Dim myCell as Range
     On Error Resume Next
     For Each myCell in rng.Cells
     'Ignore if cell length < Position of last character.
          If Len(myCell.Value) > endPos Then
               'Take the rightmost characters and delete from left.
               myCell.Value = Left(myCell.Value, startPos - 1) & _
               Right(myCell.Value, len(myCell.Value) - endPos)
          End If
     Next
     Set myCell = Nothing
     On Error GoTo 0 
End Sub

---

How to use? To run these macros, simply press F5 while placing the cursor inside the macro code.
OR From MS Excel window, press 'Alt+F8' to select the macro and then hit the 'Run' button.

---

References For more information about the functions used inside above macros, type the following text on a module sheet:

• Len
• Right
• Left
• Mid

Highlight the function about which you want more information, and then press F1.

---

Supported versions of MS Excel: APPLIES TO

• Microsoft Office Excel 2010
• Microsoft Office Excel 2007
• Microsoft Office Excel 2007
• Microsoft Office Excel 2003
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition

---