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>