Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Finding *any* cell with spaces or slashes in them, regardless of which sheet?

  Asked By: Darla    Date: Nov 07    Category: MS Office    Views: 944
  

Is there a way to find *ALL* spaces in any and all cells on any sheet? I.e.,
in workbook almost 60 worksheets deep, can I make a list of cells in columns
F, G, and H that contain spaces? It would be nice to be able to print such a
list so I can check each one for validity and correctness.

Some spaces are fine, such as “Los Angeles County” but others such as “Mc
Doo gle County” is not correct. I need to somehow make a printable list of
these containing spaces so I can check them and remove spaces where not
correct.

Same deal with “Brov/ard County.” No name (at least in my data) should have
a slash.

For those wondering how/why I have unwanted spaces in the names, it came to
me that way. I was told it was scanned into text, and the scanner didn’t
always recognize that a space was not necessary, and the software sometimes
confused the “/” when it really should be a “W.”

If it were to just tell me:
Sheet1:F:391 (or something equally informative), then I could go that
sheet, go to that cell and make any necessary corrections.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Aakifah Khan     Answered On: Nov 07

Which version of Excel are you using? 2003 will allow you to do a Find
(or a find  and Replace) across the whole workbook, clicking the 'Find
All' button will produce for you a list  (not printable) that you can
work through. But this is not necessary, you can do a find (and
replace if you want, but this replaces ALL instances of a character in
a given cell) and it will take you to the first cell  having the
offending character and allow you to edit it manually, then you can
click on 'Find Next', edit manually again, etc. etc. Excel 2000 will
only do this one sheet  at a time and does not have the 'Find All' option.

 
Answer #2    Answered By: Chelsea Rose     Answered On: Nov 07

The following macro will find  all spaces  and slashes
in column F, G, and H on every worksheet in the active
workbook, and list them  on a new sheet:

Sub FindChars()
'Declare local variables.
Dim x As Long, c As Range, NewSht As Worksheet,
HitCount As Long
On Error Resume Next
'Add a new worksheet to the current workbook  at the
end.
Worksheets.Add.Move
After:=Worksheets(Worksheets.Count)
Set NewSht = ActiveSheet
HitCount& = 1
'Check every sheet  in turn.
For x = 1 To (Worksheets.Count - 1)
DoEvents
'Activate each sheet in turn.
Sheets(x).Activate
Cells.SpecialCells(xlCellTypeConstants).Select
'If this sheet has any cells  with constant values,
select them all.
If Selection.Cells.Count > 0 Then
'Check every cell  in the selected range.
For Each c In Selection
'If it is in column F, G, or H...
If c.Column = 6 Or c.Column = 7 Or
c.Column = 8 Then
'And if it contains either character being sought...
If (InStr(c.Value, " ") > 0) Or
(InStr(c.Value, "/") > 0) Then
'List it on the new sheet.
HitCount& = HitCount& + 1
NewSht.Cells(HitCount&,
1).Value = "'" & ActiveSheet.Name
NewSht.Cells(HitCount&,
2).Value = "'" & c.Address
NewSht.Cells(HitCount&,
3).Value = "'" & c.Formula
End If
End If
Next c
End If
Next x
'If no characters were found to match, delete the new
sheet and tell user.
If HitCount& = 1 Then
MsgBox "The specified characters were not
found", vbInformation, "FindChars macro"
Application.DisplayAlerts = False
NewSht.Delete
Application.DisplayAlerts = True
GoTo FC_Cleanup
End If
'Done. Clean up. Add headings for the output rows and
resize all columns on NewSht.
NewSht.Cells(1, 1).Value = "Sheet"
NewSht.Cells(1, 2).Value = "Cell"
NewSht.Cells(1, 3).Value = "Value"
NewSht.Cells.Select
NewSht.Cells.EntireColumn.AutoFit
Calculate
NewSht.Activate
FC_Cleanup:
'Free object variables.
Set NewSht = Nothing
Set c = Nothing
MsgBox "Done!"
End Sub

Please note: this macro uses the SpecialCells property
to select cells with constants. If you need to check
cells with formulas,

change SpecialCells(xlCellTypeConstants)
to SpecialCells(xlCellTypeFormulas)

and InStr(c.Value
to InStr(c.Formula

 




Tagged: