Look for a specific string of numbers and exclude out all other number combinations containing the number string

K

KlayontKress

Board Regular
Joined
Jan 20, 2016
Messages
67
Office Version
  1. 2016
Platform
  1. Windows
To all,


I'm looking for a formula that I can use to look for a very specific number string in a column, that doesn't contain any other numbers in the cell unless the numbers are separated by "." and contained within brackets as these are used exclusively for dates, and then sum the column next to it. For example, I'm trying to look for the value "750" when it occurs as the only number in the cell , but the column of cells I'm looking in have many variation. I can't just look for #750 as show below because sometimes the 750 is by itself, sometimes it has a # out front, sometimes it has a -, sometimes it runs into a word., etc. These are manually keyed room names and there hasn't been any standardization for the last 4 years so changing it now doesn't help past data that i'm looking at now. Below is what a portion of the list looks like. the formula I'm seeking sould The record list is about 400k long so going through one at a time to count this for multiple different # strings is extremely time ocnsuming. Any help would be greatly appreciated.

The result for the below should be 189.

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] GRT RM GMT KIT 201803 *******1
#034 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] ISLANDS *******1
#263071, #263071750 PREMIER 4 ISLANDS *******5
034, 034750 ISLANDS *******3
800, 800750, 034, 034750 ISLANDS *******2
OPT #750 GMT KIT PER (5.9.18.) *******
1
OPT #750 GOURMET ******* PERIMETER (1.16.18.) *******1
OPT #750 KIT ISLAND (5.29.18.) *******2
OPT #750 ******* ISLAND (3.13.18.) *******2
OPTION # 034 750 GREAT ROOM w GOURMET ******* *******1
OPTION # 034, [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] GREAT ROOM ISLAND *******3
OPTION # 034750263072 GREAT ROOM w GOURMET KIT w *******3
OPTION # 034750263149 GREAT ROOM w GOURMET KIT w *******5
OPTION # 034750263149, [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 263149[/URL] ISLAND *******6
OPTION # 750 GOURMET ******* (71618) *******1
OPTION # 750 GOURMET ******* *******29
OPTION # 800 750 EXPANDED GOURMET ******* *******4
OPTION # 800, #800750 ISLAND *******4
OPTION # 800, #800750 ISLAND *******2
OPTION # 800750 EXPANDED ******* w GOURMET KITCHE *******3
OPTION # 886 750 ALT GOURMET ISLAND *******1
OPTION # 886, #886#750 ISLAND *******4
OPTION # 886750 ALT GOURMET ******* *******2
OPTION # 886750 ALTERNATE GOURMET ******* *******8
OPTION #034 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] GREAT ROOM ISLANDS *******18
OPTION #034 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] ISLANDS *******1
OPTION #034 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] ISLAND *******6
OPTION [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] GREAT ROOM ADD GOURMET ******* *******1
OPTION [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] GREAT ROOM GOURMET ******* *******1
OPTION [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=034]#034 750[/URL] GREAT ROOM w GOURMET ******* *******27
OPTION [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=135]#135 750[/URL] ADDITIONAL CLOSET w GOURMET KITCHE *******1
OPTION #263071750 PREMIER 4 GOURMET ******* *******3
OPTION #263149750 ALTERNATE ******* ISLAND w GOUR *******1
OPTION #750 GOURMET ISLAND *******
1
OPTION #750 GOURMET ******* *******41
OPTION #750 ISLAND (22618) *******1
OPTION #750 ISLAND (22818) *******1
OPTION #750 ISLAND *******1
OPTION #866 , #866 750, #886132188 , # 8861321887 *******2
OPTION 032033750 ELITE GOURMET ISLAND *******3
OPTION 032033750 ELITE GOURMET ******* *******4
OPTION 032750 ELITE GOURMET ISLAND *******1
OPTION 034, 034750, 034263071, 034750263071, 0 *******4
OPTION 034750 GREAT ROOM ADDITION w GOURMET KITCH *******2
OPTION 034750 GREAT ROOM W GOURMET ******* *******2
OPTION 263071 AND 263071750 ISLANDS *******1
OPTION 263071, 263071750 ISLANDS *******9
OPTION 263071750 PREMIER 4FT EXTENSION W GOURMET *******11
OPTION 263071750 PREMIER 4FT EXTENSION W GOURMET *******1
OPTION 263072 AND 263072750 ISLANDS *******1
OPTION 263072, 263072750 ISLANDS *******7
OPTION 263072750 MARQUEE 8FT EXTENSION W GOURMET *******14
OPTION 263072750 MARQUEE 8FT EXTENSION W GOURMET *******1
OPTION 529750 NAPLES SUNROOM W GOURMET ******* *******6
OPTION 750 GOURMET ******* *******
71
OPTION 750 ISLANDS *******1
OPTION 750, 760 ISLAND *******1
OPTION 800, 800750, 263149, 263149750 ISLAND *******1
OPTION 800023, 800750023 ISLAND *******3
OPTION 800750 EXPANDED GOURMET ******* *******1
OPTION 800750023 EXPANDED GOURMET KIT W EXPANDED *******2
OPTION 886, 886750 ALTERNATE ISLAND *******1
Replacement Credit OrgCntrlID = 119547500
Replacement Credit OrgCntrlID = 138677500
STANDARD AND 750 ISLAND *******
2
STANDARD AND OPT 750ISLANDS *******2
STANDARD HALL BATH, OPTION [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=075]#075 016[/URL] , OPTION #075 , Bath1
STANDARD, #750 ISLAND *******
9
STANDARD, #750 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 171[/URL] , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 172[/URL] ISLAND *******2
STANDARD, #750 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 323[/URL] , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 317[/URL] ISLANDS *******1
STANDARD, #750 , #135 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=135]#135 750[/URL] ISLAND *******6
STANDARD, #750 , #263163 ISLANDS *******7
STANDARD, #750 , #532 , #529 , #529 750 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 171[/URL] , #132 *******1
STANDARD, #750 , #760 ISLAND *******3
STANDARD, #750 , #760 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 323[/URL] , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 171[/URL] , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 172[/URL] IS *******9
STANDARD, #750 , #812 , #812 750 ISLANDS *******6
STANDARD, #760 , #750 ISLAND *******10
STANDARD, #760 , #750 , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 323[/URL] , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 171[/URL] , [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=132]#132 172[/URL] IS *******7
STANDARD, 750 ISLANDS *******
14
STANDARD, 750, 027, 027750, 027760, 529, 529750 *******13
STANDARD, 750, 132171, 132172 ISLANDS *******2
STANDARD, 750, 263163, 760, 132171,132172 ISLANDS *******1
STANDARD, 750, 760, 529, 529750, 132171, 132172I *******1
STANDARD, 750, 812, 812750, 263094 ISLAND *******6
STANDARD, 750ISLANDS *******
7
STANDARD, OPT 750ISLAND *******3

<tbody>
</tbody>


Thanks,
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Hi

If I understand correctly you just have to do 2 tests.

- The string contains the number 750
- The string contains only 3 digits
 
Upvote 0
Hi,

Try this : (result is 187....?)

C2 =IF(SUMPRODUCT(--(IFERROR(SEARCH({1,2,3,4,6,8,9},IFERROR(SUBSTITUTE(A2,IF(IFERROR(FIND(".",MID(A2,FIND("(",A2),10)),"")<>"",MID(A2,FIND("(",A2),10),""),""),A2))>0,0)))>0,0,IF(SEARCH(750,A2),1,0))

D2 =B2*C2


ABCD
1StringNumberHelperTotal
2#034 750 GRT RM GMT KIT 201803 *******100
3#034 , #034 750 ISLANDS *******100
4#263071, #263071750 PREMIER 4 ISLANDS *******500
5034, 034750 ISLANDS *******300
6800, 800750, 034, 034750 ISLANDS *******200
7OPT #750 GMT KIT PER (5.9.18.) *******111
8OPT #750 GOURMET ******* PERIMETER (1.16.18.) *******111
9OPT #750 KIT ISLAND (5.29.18.) *******212
10OPT #750 ******* ISLAND (3.13.18.) *******212
11OPTION # 034 750 GREAT ROOM w GOURMET ******* *******100
12OPTION # 034, #034 750 GREAT ROOM ISLAND *******300
13OPTION # 034750263072 GREAT ROOM w GOURMET KIT w *******300
14OPTION # 034750263149 GREAT ROOM w GOURMET KIT w *******500
15OPTION # 034750263149, #034 263149 ISLAND *******600
16OPTION # 750 GOURMET ******* (71618) *******100
17OPTION # 750 GOURMET ******* *******29129
18OPTION # 800 750 EXPANDED GOURMET ******* *******400
19OPTION # 800, #800750 ISLAND *******400
20OPTION # 800, #800750 ISLAND *******200
21OPTION # 800750 EXPANDED ******* w GOURMET KITCHE *******300
22OPTION # 886 750 ALT GOURMET ISLAND *******100
23OPTION # 886, #886#750 ISLAND *******400
24OPTION # 886750 ALT GOURMET ******* *******200
25OPTION # 886750 ALTERNATE GOURMET ******* *******800
26OPTION #034 , #034 750 GREAT ROOM ISLANDS *******1800
27OPTION #034 , #034 750 ISLANDS *******100
28OPTION #034 , #034 750 ISLAND *******600
29OPTION #034 750 GREAT ROOM ADD GOURMET ******* *******100
30OPTION #034 750 GREAT ROOM GOURMET ******* *******100
31OPTION #034 750 GREAT ROOM w GOURMET ******* *******2700
32OPTION #135 750 ADDITIONAL CLOSET w GOURMET KITCHE *******100
33OPTION #263071750 PREMIER 4 GOURMET ******* *******300
34OPTION #263149750 ALTERNATE ******* ISLAND w GOUR *******100
35OPTION #750 GOURMET ISLAND *******111
36OPTION #750 GOURMET ******* *******41141
37OPTION #750 ISLAND (22618) *******100
38OPTION #750 ISLAND (22818) *******100
39OPTION #750 ISLAND *******111
40OPTION #866 , #866 750, #886132188 , # 8861321887 *******200
41OPTION 032033750 ELITE GOURMET ISLAND *******300
42OPTION 032033750 ELITE GOURMET ******* *******400
43OPTION 032750 ELITE GOURMET ISLAND *******100
44OPTION 034, 034750, 034263071, 034750263071, 0 *******400
45OPTION 034750 GREAT ROOM ADDITION w GOURMET KITCH *******200
46OPTION 034750 GREAT ROOM W GOURMET ******* *******200
47OPTION 263071 AND 263071750 ISLANDS *******100
48OPTION 263071, 263071750 ISLANDS *******900
49OPTION 263071750 PREMIER 4FT EXTENSION W GOURMET *******1100
50OPTION 263071750 PREMIER 4FT EXTENSION W GOURMET *******100
51OPTION 263072 AND 263072750 ISLANDS *******100
52OPTION 263072, 263072750 ISLANDS *******700
53OPTION 263072750 MARQUEE 8FT EXTENSION W GOURMET *******1400
54OPTION 263072750 MARQUEE 8FT EXTENSION W GOURMET *******100
55OPTION 529750 NAPLES SUNROOM W GOURMET ******* *******600
56OPTION 750 GOURMET ******* *******71171
57OPTION 750 ISLANDS *******111
58OPTION 750, 760 ISLAND *******100
59OPTION 800, 800750, 263149, 263149750 ISLAND *******100
60OPTION 800023, 800750023 ISLAND *******300
61OPTION 800750 EXPANDED GOURMET ******* *******100
62OPTION 800750023 EXPANDED GOURMET KIT W EXPANDED *******200
63OPTION 886, 886750 ALTERNATE ISLAND *******100
64Replacement Credit OrgCntrlID = 11954750000
65Replacement Credit OrgCntrlID = 13867750000
66STANDARD AND 750 ISLAND *******212
67STANDARD AND OPT 750ISLANDS *******212
68STANDARD HALL BATH, OPTION #075 016 , OPTION #075 , Bath100
69STANDARD, #750 ISLAND *******919
70STANDARD, #750 , #132 171 , #132 172 ISLAND *******200
71STANDARD, #750 , #132 323 , #132 317 ISLANDS *******100
72STANDARD, #750 , #135 , #135 750 ISLAND *******600
73STANDARD, #750 , #263163 ISLANDS *******700
74STANDARD, #750 , #532 , #529 , #529 750 #132 171 , #132 *******100
75STANDARD, #750 , #760 ISLAND *******300
76STANDARD, #750 , #760 , #132 323 , #132 171 , #132 172 IS *******900
77STANDARD, #750 , #812 , #812 750 ISLANDS *******600
78STANDARD, #760 , #750 ISLAND *******1000
79STANDARD, #760 , #750 , #132 323 , #132 171 , #132 172 IS *******700
80STANDARD, 750 ISLANDS *******14114
81STANDARD, 750, 027, 027750, 027760, 529, 529750 *******1300
82STANDARD, 750, 132171, 132172 ISLANDS *******200
83STANDARD, 750, 263163, 760, 132171,132172 ISLANDS *******100
84STANDARD, 750, 760, 529, 529750, 132171, 132172I *******100
85STANDARD, 750, 812, 812750, 263094 ISLAND *******600
86STANDARD, 750ISLANDS *******717
87STANDARD, OPT 750ISLAND *******313

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
KlayontKress said:
...
OPTION #750 GOURMET ******* ******* 41
OPTION #750 ISLAND (22618) ******* 1
OPTION #750 ISLAND (22818) ******* 1
...
Click to expand...

Remark: in your example you consider these 2 lines as valid but they contain 750 and other numbers that are not separated by ".".

This goes against your specification:

KlayontKress said:
I'm looking for a formula that I can use to look for a very specific number string in a column, that doesn't contain any other numbers in the cell unless the numbers are separated by "." and contained within brackets as these are used exclusively for dates, and then sum the column next to it.
Click to expand...
 
Upvote 0
Hallo,

tra this code:

Code: 
Sub T_1()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    With Cells(i, 1)
        a = InStr(1, .Value, "(")
        If a <> 0 Then
            e = InStr(a, .Value, ")") + 1
            Tx = Mid(.Value, a, e - a)
            Tx = Replace(.Value, Tx, "")
        Else
            Tx = .Value
        End If
        Tx = Replace(Tx, "750", "")
        If Not Tx Like "*#*" Then .Offset(, 2) = True
    End With
Next i
End Sub

regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
260
johnnyL
J
M
  • Question
first derivative
Replies
3
Views
689
JamesCanale
JamesCanale
S
  • Question
Consecutive points scored basketball
Replies
6
Views
3K
lrobbo314
lrobbo314
L
  • Question
I would like to see the distribution by hour and day of these entries but don't have a clue where to start...
Replies
6
Views
987
leanheart
L
C
  • Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
723
onlyadrafter
O

Forum statistics

Threads
1,214,983
Messages
6,122,583
Members
449,089
Latest member
Motoracer88

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