Count Consecutive Cells

[Trapper via OfficeKB.com]

I have a simple need: I have one column of data containing either an O or
a U. Is there a way I can ask EXCEL to tell me how many times the O
appeared in 2 consecutive cells, 3 consecutive cells... etc. Say I am
using collumn A for this
A
1 O
2 O
3 O
4 U
5 U
6 O
7 U
8 U
9 U
10 U

 

[craig trapp via OfficeKB.com]

ok both of these formulas work, thanks for the help! BUT I have a small
problem... the worksheet that I am trying to use these on was copied from a
table on a web page into EXCEL. The formula is not working on the column
of data that was copied from the web-page, BUT it does work when I enter my
own data. Is the format weird because of the web page it came from?
Anyone know? I tried highlighting the column and formatting as text to no
avail!

 

[Gord Dibben]

Craig

Web Pages leave all kinds of crap in the cells.

Try David McRitchie's TRIMALL macro on your data.

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


Gord Dibben Excel MVP