Counting number of specific number of xxx in each cell?

**blitz** · 06-01-2010, 03:23 PM

Hello all, I had been looking online to find a solution to my problem and was lucky enough to stumble on this board. I had 2 questions someone on here can hopefully solve.

When working with a group of cells (say 10x10) with a number in each cell, what is the easiest way to count the number of 3’s for each cell (ex: 23253.213 would be 3)?

Secondly, how do I change each 3 to an 8 one at a time for a cell, like change3once = 28253.213, change3twice = 28258.213, etc?

Thank you in advance

**mrice** · 06-01-2010, 04:57 PM

Your first question can be solved with

=LEN(A1)-LEN(SUBSTITUTE(A1,3,))

**blitz** · 06-01-2010, 05:46 PM

Originally Posted by mrice

Your first question can be solved with

=LEN(A1)-LEN(SUBSTITUTE(A1,3,))

Martin; Thanks for your quick response. I see it works for an excel cell, but I am unable to get it to work in Visual Basic (editing my macro). Is there something different I need to do so it will work there?

**blitz** · 06-01-2010, 06:35 PM

Originally Posted by blitz

Martin; Thanks for your quick response. I see it works for an excel cell, but I am unable to get it to work in Visual Basic (editing my macro). Is there something different I need to do so it will work there?

Finally figured out for VBA code it would be

NumberOfThrees=(Len(CellX) - Len(Application.Substitute(CellX, 3, ""))

Now to figure out the other item

**blitz** · 06-02-2010, 03:24 PM

BUMB: Still unable to figure this out

Secondly, how do I change each 3 to an 8 one at a time for a cell, like change3once = 28253.213, change3twice = 28258.213, etc?

**arthurbr** · 06-02-2010, 03:28 PM

Please use code tags when editing VBA Thx

**mrice** · 06-04-2010, 04:41 PM

Try

Sub Test()
X = 23253.213
Y = ChangeSpecial(X, 3, 8, 2)
End Sub



Function ChangeSpecial(ByVal MyNumber As Double, NumberToReplace As Integer, NumberToReplaceWith As Integer, Count As Long) As Double
Dim N As Integer, M As Integer
For N = 1 To Len(MyNumber)
    If Mid(MyNumber, N, 1) = NumberToReplace Then
        M = M + 1
        If M <= Count Then
            MyNumber = Left(MyNumber, N - 1) & NumberToReplaceWith & Right(MyNumber, Len(MyNumber) - N + 1)
        End If
    End If
Next N
ChangeSpecial = Val(MyNumber)
End Function