I need an excel formula to tell how many numbers and alphabets are in a cell

Hey Guys I need help sorting series of plate numbers out on excel.

so I have a sample BMA759JA, and I need a formula that tells me how many Alphabets are contained in the cell and how many numbers are also contained in the cell.

so it's obviously 3 Alphabets, 3 numbers, and 2 Alphabets, from the left, but how do I write a formula to give the same detail???

I've tried several things, to no avail

Onyinye

Posted 2018-07-09T17:08:22.960

Reputation: 21

1Do you want just the total counts? (e.g., 5 letters and 3 digits) Or do you need the ordered list? (e.g., 3 letters, 3 digits, 2 letters) – Nick Russo – 2018-07-09T17:33:17.157

I need the ordered list actually ( 3 letter, 3digits,2 letters) – Onyinye – 2018-07-09T17:54:16.033

OK - in that case, I suspect that the "sorting" tag won't apply. Also, this feels like there will be array formulas, unless you are willing to use Excel VBA instead? – Nick Russo – 2018-07-09T18:06:05.893

is it always 3 groupings letters,numbers,letters? If it changes and can be more mixed then vba will be the way to go. Formulas just do not have that kind of latitude. – Scott Craner – 2018-07-09T18:11:27.413

no, it's not always 3 letters and so on, it can be 2 letters, 2 numbers and 3 letters, or 2 letters, 3 numbers and 2 alphabets. but it always starts with 2 or 3 letters and contains either 7 or 8 characters in total – Onyinye – 2018-07-09T18:19:39.177

The idea of the formula is to help tell when the plate number inputed is valid or not. If it's 7 or 8 characters but starts with numbers or only one alphabet, then it's definitely not a valid plate number – Onyinye – 2018-07-09T18:20:10.713

but it is always 3 groups? – Scott Craner – 2018-07-09T18:20:11.877

yes, it always is 3 groups – Onyinye – 2018-07-09T18:24:39.373

Answers

Assuming you're taking the "BMA759JA" in a cell

so it's obviously 3 Alphabets, 3 numbers, and 2 Alphabets

Then it would always be 5 alphabets and 3 numbers.

In applying a formula, you can divide each character of the string to one cell.

cell A1: B
cell A2: M
cell A3: A
....

then you can use count function

cell A10: =count(A1)

then just add all the count results for every word

cell A19: =sum(A10:A18)

Super User User

Posted 2018-07-09T17:08:22.960

Reputation: 25

But this would give me a total count yes? not a break down? – Onyinye – 2018-07-09T17:56:11.777

Use this to validate:

=AND(SUMPRODUCT(--ISERROR(--MID(A2,{1,2},1)))=2,ISNUMBER(--MID(A2,AGGREGATE(15,6,ROW(1:8)/(ISNUMBER(--MID(A2,ROW(1:8),1))),1),AGGREGATE(14,6,ROW(1:8)/(ISNUMBER(--MID(A2,ROW(1:8),1))),1)-AGGREGATE(15,6,ROW(1:8)/(ISNUMBER(--MID(A2,ROW(1:8),1))),1))))

Scott Craner

Posted 2018-07-09T17:08:22.960

Reputation: 16 128

The following User Defined Function will return the number of letters followed by the number of numerals, followed by the number of letters as a comma-separated list like:

10,2,3

Public Function decompose(s As String) As String
    Dim breakdown(1 To 100) As Long, L As Long
    Dim i As Long, c As String, j As Long
    L = Len(s)
    breakdown(1) = 1
    j = 1
    For i = 2 To L
        c = Mid(s, i, 1)
        If typ(c) = typ(Mid(s, i - 1, 1)) Then
            breakdown(j) = breakdown(j) + 1
        Else
            j = j + 1
            breakdown(j) = 1
        End If
    Next i

    For i = 1 To j
        decompose = decompose & "," & breakdown(i)
    Next i

    decompose = Mid(decompose, 2)

End Function


Public Function typ(s As String) As String
    If s Like "[0-9]" Then
        typ = "number"
    Else
        typ = "letter"
    End If
End Function

Gary's Student

Posted 2018-07-09T17:08:22.960

Reputation: 15 540