Select cells that start with B and add a selection of each cell

[Mananaman]

Hello all please can you help me. I have a spreadsheet with 3 columns and I would like to identify all cells that start with the letter B and would like to add up a selection of the data held in each cell. The numbers to add up are all of the last numbers prior to the letter D on the end of the string, if that makes sense. I've included a sample of the data which is only 3 columns and would very much appreciate anyone's help. Kind regards

00173551		1		H00173551 0
00173551		2		NJOHN W.ADAMS
00173551		3		N4 LAIRGATE
00173551		4		NBEVERLEY
00173551		5		N
00173551		6		N
00173551		7		NHU17 8EE
00173551		8		D161221160628PEN A00174471 150930 JOHN W.A1612200000150000D 0000150000
00173551		9		B00173551 00000000000150000D
01947473		1		H01947473 0
01947473		2		NWOOD
01947473		3		N16 IRVING STREET
01947473		4		NLEICESTER SQUARE
01947473		5		NLONDON
01947473		6		N
01947473		7		NWC2H 7AU
01947473		8		D170612160930PEN A00281685 151231 WOODBLOX1706080000300000D 0000300000
01947473		9		B01947473 00000000000300000D
02815204		1		H02815204 0
02815204		2		NSTAMPER FARMS LIMITED
02815204		3		NWEST FARM OFFICE, WEST FARM
02815204		4		NBARNHAM
02815204		5		NTHETFORD
02815204		6		NNORFOLK
02815204		7		NIP24 2PL
02815204		8		D150813140228PEN A000349438130531 STAMPER 1507310000300000D 0000300000
02815204		9		B02815204 00000000000300000D
02934906		1		H02934906 0
02934906		2		NTECHNICAL SUPPORT
02934906		3		NCHAR HOUSE

 

[Nine Zero]

So if im understanding correctly from the data you would want the cells which are

B00173551 00000000000150000D
B01947473 00000000000300000D
B02815204 00000000000300000D

then add the numbers up which would be

150000
300000
300000

so your end goal would be

750000

????

 

[Mananaman]

Thanks for taking the time to look at my problem. Yes that is exactly right. Thanks

 

[JoeMo]

Assuming the data you want to extract the numbers to be added is in col C starting in C1, and that the extraction is from a string that always starts with a "B", ends with a "D", and has a space just before the numbers to be extracted, this macro will report the sum of the digits.

Sub AddDigits()
Dim X As Variant, i As Long, S
X = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Value
For i = 1 To UBound(X, 1)
    If Left(X(i, 1), 1) = "B" And Right(X(i, 1), 1) = "D" Then
        X(i, 1) = Replace(X(i, 1), "D", "")
        S = S + Val(Mid(X(i, 1), InStrRev(X(i, 1), " "), Len(X(i, 1))))
    End If
Next i
If S <> "" Then MsgBox "The sum of digits for the cells starting with B and ending in D is: " & Format(S, "#,##0")
End Sub

 

[Nine Zero]

Assuming your string are in column C try this out

Sub SumStuff()
Dim r As Range
Dim x As String
Dim y, total As Long

'change your range here 
For Each r In Range("C1:C20")
    If Left(r, 1) = "B" Then
        x = Mid(r.Value, InStr(1, r.Value, " "), InStr(1, r.Value, "D"))
        y = Left(x, Len(x) - 1)
        total = total + y
    End If
Next
'if you dont want a msgbox just place it in some range
MsgBox total

End Sub

 

[Rick Rothstein]

You have two macros (although I probably would have written them as UDFs... user defined functions), but in case you are interested, there is an array-entered** formula solution available...

[table="width: 500"]
[tr]
	[td]=SUM(IF(ISNUMBER(SEARCH("B*D",C1:C[COLOR="#FF0000"][B]999[/B][/COLOR]))*(LEFT(C1:C[B][COLOR="#FF0000"]999[/COLOR][/B])="B"),0+MID(C1:C[B][COLOR="#FF0000"]999[/COLOR][/B],FIND(" ",C1:C[B][COLOR="#FF0000"]999[/COLOR][/B]&" "),LEN(C1:C[B][COLOR="#FF0000"]999[/COLOR][/B])-FIND(" ",C1:C[B][COLOR="#FF0000"]999[/COLOR][/B]&" ")),0))
[/td]
[/tr]
[/table]

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: If you have more than 999 rows of data, you need to change the six 999s in the formula to a row number guaranteed to be equal to or greater than the maximum row number you ever expect to have data for.

 

[Rick Rothstein]

You have two macros (although I probably would have written them as UDFs... user defined functions), but in case you are interested, there is an array-entered** formula solution available...

[table="width: 500"]
[tr]
	[td]=SUM(IF(ISNUMBER(SEARCH("B*D",C1:C[COLOR="#FF0000"][B]999[/B][/COLOR]))*(LEFT(C1:C[B][COLOR="#FF0000"]999[/COLOR][/B])="B"),0+MID(C1:C[B][COLOR="#FF0000"]999[/COLOR][/B],FIND(" ",C1:C[B][COLOR="#FF0000"]999[/COLOR][/B]&" "),LEN(C1:C[B][COLOR="#FF0000"]999[/COLOR][/B])-FIND(" ",C1:C[B][COLOR="#FF0000"]999[/COLOR][/B]&" ")),0))
[/td]
[/tr]
[/table]

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: If you have more than 999 rows of data, you need to change the six 999s in the formula to a row number guaranteed to be equal to or greater than the maximum row number you ever expect to have data for.

Given that the above formula works, we can use it to make a somewhat compact UDF (user defined function), well, not so much "compact" as the code line is quite long, but I do note that the code is non-looping if you are interested in such things...

[table="width: 500"]
[tr]
	[td]Function SumBtoD()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  SumBtoD = Evaluate(Replace("SUM(IF(ISNUMBER(SEARCH(""B*D"",C1:C#))*(LEFT(C1:C#)=""B""),0+MID(C1:C#,FIND("" "",C1:C#&"" ""),LEN(C1:C#)-FIND("" "",C1:C#&"" "")),0))", "#", LastRow))
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SumBtoD just like it was a built-in Excel function. For example,

=SumBtoD()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Mananaman]

Thanks JoeMo for the response. I'll give it a go today! Regards

 

[Mananaman]

Thanks everyone for your quick responses. My company has restricted my Excel so unfortunately I can't use the macros, however Rick's formula is awesome and works perfectly! Thank you so much.