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.
- Display the Developer tab in Excel
- To display the Developer tab, click on Preferences under the Excel menu at the top of the screen
- In Excel Preferences window , click on the Ribbon icon in the Sharing and Privacy section
- In the Customize section, check Developer in the list of tabs to show. Then click on the OK button.
- or read this tutorial http://www.techonthenet.com/excel/macros/visual_basic_editor2011.php
-
Click Developer Tab in Excel
-
Click Editor in the toolbar
-
Double click Sheet1....
-
Copy and Paste the following code into the Sheet1... window :
-
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
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
- The Sheet1... window should display: Do NOT close the window but move it or minimize it if it is in the way
-
Click Macros in the toolbar
-
Click Run:
-
Now all carriage returns and line feeds and tab delimiters have been removed !
-
Final step: Export to a TAB delimited file Read this article
-
In the NetMix Encoder click Select TAB File and select Open as MacRoman
and select the tab delimited text file you just saved
0 Comments