Delete Only the Text from Cells

Edit
Add to Favorites
Author:
This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cells. That means that if you are trying to get letters out of part numbers (etc.) and you run this macro, the numbers and other characters will remain but all text will be gone.

This is a great macro for scrubbing or cleaning large amounts of data. This will remove capitalized and lowercase text; it will remove all text from the cells.

This macro works on a selection of cells. That means that it will only remove text from cells which you select. This way it is easier to control what is deleted.

Where to install the macro:  Module

Delete Text Only from Cells

Sub Remove_Text_From_Cells()
 
    ‘Removes text from cells.
    ‘Will not remove any characters other than text
 
    Dim rngCell As Range
    Dim intChar As Integer
    Dim strCheckString As String
    Dim strCheckChar As String
    Dim intCheckChar As Integer
    Dim strClean As String
 
    For Each rngCell In Selection
        strCheckString = rngCell.Value
        strClean = ""
 
        For intChar = 1 To Len(strCheckString)
            strCheckChar = Mid(strCheckString, intChar, 1)
            intCheckChar = Asc(strCheckChar)
            Select Case intCheckChar
                Case 65 To 90       
                Case 97 To 122      
                Case 128 To 151     
                Case 153 To 154     
                Case 159 To 165     
                Case Else
                    strClean = strClean & strCheckChar
            End Select
 
        Next intChar
        rngCell.Value = strClean
 
    Next rngCell
 
End Sub





Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the fo...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to ...
3 Methods to Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all space...
Count the Number of Cells that Start or End with Specific Text in Excel
Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Delete All Comments in a Worksheet in Excel Macro
Macro: Excel macro that will delete all of the comment contained within the active or current wor...


How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.

Tutorial Details
Similar Content
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the fo...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to ...
3 Methods to Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all space...
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course