# 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?

• 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.

• Re: Count letters till you get to a number

Guys That is Awsome! Thank you very much!

## Participate now!

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