Count letters till you get to a number

  • Is there a way to count the number of letters until you get to a number? For instance:


    Coke 12pk Can
    Diet Coke 6pk Can
    Cherry Coke 6pk Can
    Coke 6pk Can


    I need to do a brand breakdown, by summing totals for each brand such as Coke, Diet Coke, Cheery Coke etc...


    Some brands have 3 words, some have 2 words, the only similarities is the Brand name ends at the first number, is there a way to count the letters up to the number?


    Thanks in advance

  • Re: Count letters till you get to a number


    The following user defined function (UDF) will count the characters (including blanks) up to the first number.
    [vba]Function CountToNumber(r)
    Dim i As Integer
    For i = 1 To Len(r)
    If IsNumeric(Mid(r, i, 1)) Then
    CountToNumber = i - 1
    Exit Function
    End If
    Next i
    CountToNumber = Len(r)
    End Function[/vba]

  • Re: Count letters till you get to a number


    To include spaces...


    =LEN(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))


    To exclude spaces...


    =LEN(SUBSTITUTE(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)," ",""))


    Hope this helps!

  • Re: Count letters till you get to a number


    as long as you data is in column "A" this should do the trick


    :drum: The worst moment for the atheist is when he is really thankful and has nobody to thank.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!