I have columns like so:

SW1 Amount Data
SW18 Amount Data
SW19 Amount Data
B3 Amount Data
B22 Amount Data

etc

Basically I just want to group the data by the first letter bit, so B3
and B22 is summed just becomes B data, and SW1/SW18/SW19 is summed and
becomes SW data.

Having trouble because the letter and number size varies but I'm sure
this is easy to do?

Thanks

Chris Ashley wrote:
I have columns like so:

SW1 Amount Data
SW18 Amount Data
SW19 Amount Data
B3 Amount Data
B22 Amount Data

etc

Basically I just want to group the data by the first letter bit, so B3
and B22 is summed just becomes B data, and SW1/SW18/SW19 is summed and
becomes SW data.

Having trouble because the letter and number size varies but I'm sure
this is easy to do?

Thanks

Chris,
Since you are posting this is a vba group I assume you want code
rather than a worksheet function. So:

Function LetterPart(S As String) As String
Dim i As Long, j As Long
On Error GoTo ReturnNow
For i = Len(S) To 1 Step -1
j = CLng(Mid(S, i))
Next i
ReturnNow:
LetterPart = Mid(S, 1, i)
End Function

Function NumberPart(S As String) As Long
Dim i As Long, j As Long
On Error GoTo ReturnNow
For i = Len(S) To 1 Step -1
j = CLng(Mid(S, i))
Next i
ReturnNow:
NumberPart = j
End Function

Then LetterPart("SW12") returns "SW" and NumberPart("SW12") returns 12.
If your input string isn't of the specified form then the "letter part"
might contain non-letters, so maybe the name isn't robust (the letter
part is the part of the string up to but not including any integer
tail).
The idea behind the code is that CLng("12") returns 12 but CLng("W12")
throws an error (CLng() converts to (long) integers and "W12" can't be
so converted). The code starts at the end of the string and works
backwards. When an exception is thrown it knows that it has reached the
end of the letter part. If your number parts gets into the billions and
can't be put into a Long then the code breaks, but for your examples
that doesn't seem an issue.

You could put the code in a public code module and call it from a
worksheet if needed.

Hope that helps

-John Coleman