How to remove all returns and tabs using a macro in Excel 2011

The NetMix Encoder's text parsing function displays an error message like "Line 68 has only 115 tabs" when it encounters carriage return, line feed or tab characters in a cell.

It is therefore necessary to remove these special characters.

This article describes how to remove all


  • carriage returns chr(13)
  • line feeds chr(10)
  • tabs chr(9)


from all cells in a Excel spreadsheet.

This example uses Excel 2011 for Mac OS X.

  1. Display the Developer tab in Excel
    1. To display the Developer tab, click on Preferences under the Excel menu at the top of the screen
    2. In Excel Preferences window , click on the Ribbon icon in the Sharing and Privacy section
    3. In the Customize section, check Developer in the list of tabs to show. Then click on the OK button.
      1. or read this tutorial
  2. Click Developer Tab in Excel a0e343c2119686ae3e1686d039db887c.jpeg

  3. Click Editor  in the toolbar ce34773d85aad667b35c8ee6acca037e.jpeg

  4. Double click Sheet1.... e65bfc0f83f1576cfae0e18613d755b3.jpeg

  5. Copy and Paste the following code into the Sheet1... window :

  6. Sub RemoveCarriageReturns()

        Dim MyRange As Range

        Application.ScreenUpdating = False

        Application.Calculation = xlCalculationManual

        For Each MyRange In ActiveSheet.UsedRange

            If 0 < InStr(MyRange, Chr(10)) Then

                MyRange = Replace(MyRange, Chr(10), "")

            End If

            If 0 < InStr(MyRange, Chr(13)) Then

                MyRange = Replace(MyRange, Chr(13), "")

            End If

            If 0 < InStr(MyRange, Chr(9)) Then

                MyRange = Replace(MyRange, Chr(9), "")

            End If


        Application.ScreenUpdating = True

        Application.Calculation = xlCalculationAutomatic

    End Sub

  7. The Sheet1... window should display: Do NOT close the window but move it or minimize it if it is in the waybdb08c398c4aaa6692a4062c1cdac6ad.png
  8. Click Macros in the toolbar ec1d0f8c2cb3614bed8c8795b9e410a4.jpeg

  9. Click Run9f631d03efa5ab831cc6f0a7fe41440f.jpeg

  10. Now all carriage returns and line feeds and tab delimiters have been removed !

  11. Final step: Export to a TAB delimited file Read this article


    In the NetMix Encoder click Select TAB File and select Open as MacRoman 570d0b6f52341adbf5e90ddca72bad15.pngand select the tab delimited text file you just saved


Have more questions? Submit a request


Article is closed for comments.
Powered by Zendesk