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

M

Mananaman

New Member
Joined
Aug 4, 2015
Messages
12
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

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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

????
 
Upvote 0
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.
Code: 
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
 
Upvote 0
Assuming your string are in column C try this out

Code: 
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
 
Upvote 0
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...
Code: 
[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.
 
Last edited:
Upvote 0
Rick Rothstein said:
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...
Code: 
[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.
Click to expand...
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...
Code: 
[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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
279
myall_blues
myall_blues
O
  • Solved
populate amounts for adjacent cell for each account name and add difference amount
Replies
4
Views
179
Omar M
O
SaraWitch
  • Solved
Add zero to beginning of number extraction
2
Replies
10
Views
711
MARK858
MARK858
S
  • Solved
Add the number to True
2
Replies
19
Views
209
sksanjeev786
S
S
  • Solved
Embedding IF in single cell formula
Replies
3
Views
157
sharshra
S

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top