CODEWITHSUNDEEP
excelexcel-formula
Ads5
Ads5

Reputation: 21

Excel counting seperate values in the same cell

I have a excel sheet in which I need to count diffrent values from one cell.
The ones and zeros vary as well as the amount of <br> that seperated them. I need to count the ones and zeros seperatly. 10000010011<br>10101010101<br>01100111000<br>101101010110 In this example I need four values indicating the amount of ones and zeros. Here it would be: zeros1:7 ones1:4, zeros2: 5 ones2:6, zeros3: 6 ones3: 5, zeros4: 5 ones4: 7. I really would appreciate any suggestions!

Upvotes: 0

Views: 94

Answers (4)

Ads5
Ads5

Reputation: 21

Dear all thanks for your suggestions! Basically I have deceided to it the simple but more lengthly way in splitting the cell (text in columns).

Upvotes: 0

Mnaukal
Mnaukal

Reputation: 36

You can use the LEFT and RIGHT functions in combination with SEARCH to split the cell by "<br>". You can put them in a row (into multiple cells), so that the next one takes output of the previous one as the input.

So A1 would be your input and then put =RIGHT(A1,LEN(A1)-FIND("<br>",A1,1) - 3) into A2 (this will remove everything before first "<br>"). Then put the same formula, but with A2 instead of A1 everywhere (that will remove everything before second "<br>"), and so on.

More info: https://support.office.com/en-us/article/split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

If a VBA answer is ok you could use this function:

With 10000010011<br>10101010101<br>01100111000<br>101101010110 in cell A1 the formula =Count1And0(A1) will return 4:7, 6:5, 5:6, 7:5

Public Function Count1And0(Target As Range) As String

    Dim vSplit As Variant
    Dim vNum As Variant
    Dim sFinal As String
    Dim lCount As Long

    vSplit = Split(Target, "<br>")

    For Each vNum In vSplit
        lCount = Len(vNum) - Len(Replace(vNum, "0", ""))
        sFinal = sFinal & Len(vNum) - lCount & ":" & lCount & ", "
    Next vNum

    Count1And0 = Left(sFinal, Len(sFinal) - 2)

End Function

Upvotes: 0

pnuts
pnuts

Reputation: 59495

Assuming each binary is always 11 digits:

Parse your data Text to Columns/Fixed width with breaks around the angled brackets, then skip the <br> columns and apply formulae such as:

=LEN(A1)-LEN(SUBSTITUTE(A1,1,""))

copied across to suit and repeat with ,0, in place of ,1, or just subtract the above results from 11.

Upvotes: 1

Related Questions