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
Sub countletters()
For rownum = 1 To Cells(65000, 1).End(xlUp).Row
num_string = ""
For x = 1 To Len(Cells(rownum, 1).Value)
Cells(rownum, 2) = Mid(Cells(rownum, 1).Value, x, 1)
Cells(rownum, 3).FormulaR1C1 = "= RC[-1]*1"
If Not IsError(Cells(rownum, 3)) Then num_string = num_string & Cells(rownum, 2)
Next x
Cells(rownum, 2) = num_string * 1
Cells(rownum, 3).ClearContents
Next rownum
End SubRe: Count letters till you get to a number
Guys That is Awsome! Thank you very much!
Don’t have an account yet? Register yourself now and be a part of our community!