Change Carriage Returns and Tabs to spaces

dboeckm

New Member
Joined
Sep 8, 2002
Messages
2
Is there a way to change all carriage returns
and tabs to spaces in all cells in
a spreadsheet?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
On 2002-09-09 11:13, dboeckm wrote:
Is there a way to change all carriage returns
and tabs to spaces in all cells in
a spreadsheet?

Hi dboekm
You could try runing this macro for the sheet that has these.
What this routines does is to clean & trim ALL text cells of these formats.

<pre/>
Sub Clean_Trim()
'// From Help Files:
'// CLEAN > Removes all nonprintable characters from text.
'// Use CLEAN on text imported from other applications that
'// contains characters that may not print with your operating system.
'// For example, you can use CLEAN to remove some low-level computer code
'// that is frequently at the beginning and end of data files and cannot be printed.

'// TRIM > Removes all spaces from text except for single spaces between words.
'// Use TRIM on text that you have received from another application that may
'// have irregular spacing.

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next

End Sub
</pre>
 
Upvote 0
Ivan, thanks for getting us started in
the right direction. Here is the
complete macro that we use
to replace carriage returns and tabs
with spaces.

Sub RemoveCR()

Dim CleanRg As Range
Dim oCell As Range
Dim lineText As String
Dim Pos, Count As Integer
Dim Ret As String

On Error Resume Next
Set CleanRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean!": Exit Sub
Conut = 0

For Each oCell In CleanRg
lineText = oCell.Text
Ret = Chr(10) 'Look for LF (0A hex)
Pos = InStr(lineText, Ret)
While (Pos > 0)
Mid(lineText, Pos, 1) = " "
Count = Count + 1
oCell = lineText
Pos = InStr(lineText, Ret)
Wend
Ret = Chr(9) 'Look for TAB (09 hex)
Pos = InStr(lineText, Ret)
While (Pos > 0)
MsgBox "Tab found in Cell " & lineText
Mid(lineText, Pos, 1) = " "
oCell = lineText
Pos = InStr(lineText, Ret)
Wend
Next
MsgBox "Changed a total of " & Count & " CRs"

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,165
Messages
6,170,464
Members
452,329
Latest member
Irefsports

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top