Count numeric cells displaying as ## hash marks (cells contain numbers but display as pound signs)

I thought this would work, but it doesn't.  =countif(a1:a5,"*#*")

To demonstrate, put 999999 into a1 through a5 then make the column 1 character wide.

I have a workaround that uses vba, but it is very slow. It uses .cells.find (what:="#") and loops through the selected range.

I would much prefer an solution that uses an excel worksheet function.
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncanb7Commented:
It may be better doing on VBA

For Excel cell,I try to compare the length of cell and cell width to solve the issue that is  what I can do but that is not good one if narrow width is equal to one character,  If length of cell is greater than cell width, it means the cell has pound sign(#).
please try it  as follows for at least narrow width=2.For narrow width=1, it will work sometimes and
sometimes not. So you need to work around for this


=SUMPRODUCT(--(LEN(A1:A5)>CELL("width",A1)))

Be reminded need to do automatic update cell formula for refresh cell if the columnwidth is changed by Excel: tools->options.caluation->calcuation->automatic

Hope understand your question completely.If not, please point it out
Duncan
0
Martin LissOlder than dirtCommented:
You can't use worksheet functions because they all look at the stored value of the cells and not at their appearance.

With 100,000 rows this takes only 1.5 seconds to run.

Sub CountPoundSigns()
Dim lngCount As Long
Dim lngRow As Long

For lngRow = 1 To ActiveSheet.UsedRange.Rows.Count
    If InStr(1, Cells(lngRow, 1).Text, "#") Then
        lngCount = lngCount + 1
    End If
Next
MsgBox lngCount
End Sub

Open in new window

0
rberkeConsultantAuthor Commented:
Duncan: I should have mentioned the cells are formatted, so Len function will not work at all.

Martin, the goal was something faster than my workaround. Your solution is simpler but a LOT slower.

I suspect that there is no solution that is better than mine, but I will leave the problem open for a while longer in hopes of another approach.

rberke

Sub CountPoundSigns()
Dim rng As Range


Dim cell As Object, firstaddress As String, fnd As Range


With ActiveSheet.UsedRange.Columns(1)
Set cell = .Find(what:="#", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False, SearchOrder:=xlByRows)

If Not cell Is Nothing Then
    firstaddress = cell.Address
    Do
        If fnd Is Nothing Then
            Set fnd = cell
        Else
            Set fnd = Union(fnd, cell)
        End If
        Set cell = .FindNext(cell)
   
    Loop While cell.Address <> firstaddress
End If
End With

MsgBox fnd.Cells.Count
Exit Sub
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

duncanb7Commented:
Please let us know what you want to let it work on Excel cell formula  or VBA ?

it seems VBA is better, and the speed is not such slow
0
duncanb7Commented:
so len() is not related to the cell formated or not, just
make simple example on a new sheet and test my code for Excel cell before
concluding it

Duncan
0
rberkeConsultantAuthor Commented:
It cannot work reliably. Demonstrate yourself.  Put 123456 into cell a1 and a2. Format a1 as $#,##0.00 and a2 as #0

You will see a1 displays ###  and a2 displays 123456.  But then both have len = 6
0
Martin LissOlder than dirtCommented:
Martin, the goal was something faster than my workaround. Your solution is simpler but a LOT slower.
Mine took 1.5 seconds for 100,000 records. Out of curiosity how long did your's take? BTW if your interested I have an article on timing code if you want an accurate number.
0
duncanb7Commented:
I tried that works at my side  no any issue at all

Just open a new sheet to test  those suggestion from the posts

Duncan
0
duncanb7Commented:
Doing it on Excel cell that is not easy one for your case even I tried it to closer one. I suggest you
to do it on VBA  to pick easier way

Have a nice day , talk your tomorrow

Duncan
0
rberkeConsultantAuthor Commented:
On my machine your code took 6 seconds for 20000 rows.
my code took less than a second.

When I originally posted this question, I had not actually implemented my workaround, so I did not realize how fast it would be.  It turns out it is blazing fast, so I have actually accomplished what I wanted.

I will close the question.
0
rberkeConsultantAuthor Commented:
I simplified my code, then did a little more testing.  It turns out that Martin's code is faster than my code when more than 20% of the cells  have ###,   For instance, when I had 20000 cells which ALL had ###, My code took 5 times longer than Martin's.  For my purposes, this rarely happens, so I will continue to use my own code. Nonetheless, Martin's might be "better" for other people.


Sub CountPoundSigns()
 Dim time As Double
 time = Now()   '  AccurateNow()  '  < my personal.xls has an AccurateNow function which is far more accurate than Now().


 Dim cell As Object, cellA As Range, cnt As Long

 With ActiveSheet.UsedRange.Columns(1)
     
     Set cellA = .find(what:="#", LookIn:=xlValues, LookAt:=xlPart, _
            matchcase:=False, SearchOrder:=xlByRows)
   
     If Not cellA Is Nothing Then
        Set cell = cellA
         Do
            cnt = cnt + 1
            Set cell = .FindNext(cell)
         Loop While cell.Address <> cellA.Address
     End If
 End With

 MsgBox cnt & " " & (Now()- time) * 24 * 60 * 60

 End Sub
0
rberkeConsultantAuthor Commented:
Martin's vba code was simple and worked, so I gave him points. My own code is more complicated by much faster so I gave myself the best answer.

Duncan's attempt avoided VBA. so it would have been ideal. Unfortunately, it did not work if any of the numbers had decimals, commas or other formatting.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.