CLEAN

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

How do I go about using the CLEAN ( or TRIM function).

I wish to clean a cell and put the result back in that same cell, however
now I get circular errors.

Thanks
 
I wish to clean a cell and put the result back in that same cell, however
now I get circular errors.
Click to expand...

Do you mean #REF! (?). You have to hard-code your results first. With
something in A1, and =TRIM(A1) in B1, copy B1 then select A1 and Paste
Special >> Values >> OK. Then you can del B1.

HTH,
Andy
 
You may be better off using a macro.

Possibly. OTOH, try Google's "I'm Feeling Lucky" for "french military
victories".

<bg>,
Andy
 
Hi,

Thanks.

I have about 120 sheets spread over 2 files - is there anyway I can automate
this macro to run in every sheet without having to do it manually!

(BTW the macro is:

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Thanks
 
Dave or someone else will probably post a much simpler way without selecting, but the following
quick and dirty tweak should work for you - May take a bit of time though.

Sub TrimALLSheets()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
For x = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(x).Activate
ActiveSheet.UsedRange.Select
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Range("A1").Select
Next x
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
I think the easiest way is to just have another procedure loop through the
sheets:

Sub doAll()

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Select
.Cells.Select
Call TrimALL
.Range("a1").Select
End With
Next wks

End Sub

(make sure you keep the TrimALL procedure)

(You'll have to run it twice--once for each workbook.)
 
Dave Peterson gave you a good routine to apply it to all sheets, but the
routine doesn't do the clean function except for character 160. I would
enrich it with

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(Application.Clean(cell.Value))
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If you have nonprinting/special characters beside 160.

Regards,
Tom Ogilvy
 
Back
Top