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.

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


Thanks,
 

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
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


[TABLE="width: 757"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]String[/TD]
[TD]Number[/TD]
[TD]Helper[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: left"]#034 750 GRT RM GMT KIT 201803 *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: left"]#034 , #034 750 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]#263071, #263071750 PREMIER 4 ISLANDS *******[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: left"]034, 034750 ISLANDS *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: left"]800, 800750, 034, 034750 ISLANDS *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: left"]OPT #750 GMT KIT PER (5.9.18.) *******[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: left"]OPT #750 GOURMET ******* PERIMETER (1.16.18.) *******[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: left"]OPT #750 KIT ISLAND (5.29.18.) *******[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: left"]OPT #750 ******* ISLAND (3.13.18.) *******[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: left"]OPTION # 034 750 GREAT ROOM w GOURMET ******* *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: left"]OPTION # 034, #034 750 GREAT ROOM ISLAND *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: left"]OPTION # 034750263072 GREAT ROOM w GOURMET KIT w *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: left"]OPTION # 034750263149 GREAT ROOM w GOURMET KIT w *******[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: left"]OPTION # 034750263149, #034 263149 ISLAND *******[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: left"]OPTION # 750 GOURMET ******* (71618) *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: left"]OPTION # 750 GOURMET ******* *******[/TD]
[TD]29[/TD]
[TD]1[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: left"]OPTION # 800 750 EXPANDED GOURMET ******* *******[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: left"]OPTION # 800, #800750 ISLAND *******[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: left"]OPTION # 800, #800750 ISLAND *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: left"]OPTION # 800750 EXPANDED ******* w GOURMET KITCHE *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: left"]OPTION # 886 750 ALT GOURMET ISLAND *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: left"]OPTION # 886, #886#750 ISLAND *******[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: left"]OPTION # 886750 ALT GOURMET ******* *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="align: left"]OPTION # 886750 ALTERNATE GOURMET ******* *******[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD="align: left"]OPTION #034 , #034 750 GREAT ROOM ISLANDS *******[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD="align: left"]OPTION #034 , #034 750 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD="align: left"]OPTION #034 , #034 750 ISLAND *******[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD="align: left"]OPTION #034 750 GREAT ROOM ADD GOURMET ******* *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD="align: left"]OPTION #034 750 GREAT ROOM GOURMET ******* *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD="align: left"]OPTION #034 750 GREAT ROOM w GOURMET ******* *******[/TD]
[TD]27[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD="align: left"]OPTION #135 750 ADDITIONAL CLOSET w GOURMET KITCHE *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD="align: left"]OPTION #263071750 PREMIER 4 GOURMET ******* *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD="align: left"]OPTION #263149750 ALTERNATE ******* ISLAND w GOUR *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD="align: left"]OPTION #750 GOURMET ISLAND *******[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD="align: left"]OPTION #750 GOURMET ******* *******[/TD]
[TD]41[/TD]
[TD]1[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD="align: left"]OPTION #750 ISLAND (22618) *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD="align: left"]OPTION #750 ISLAND (22818) *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD="align: left"]OPTION #750 ISLAND *******[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD="align: left"]OPTION #866 , #866 750, #886132188 , # 8861321887 *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD="align: left"]OPTION 032033750 ELITE GOURMET ISLAND *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD="align: left"]OPTION 032033750 ELITE GOURMET ******* *******[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD="align: left"]OPTION 032750 ELITE GOURMET ISLAND *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD="align: left"]OPTION 034, 034750, 034263071, 034750263071, 0 *******[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD="align: left"]OPTION 034750 GREAT ROOM ADDITION w GOURMET KITCH *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD="align: left"]OPTION 034750 GREAT ROOM W GOURMET ******* *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD="align: left"]OPTION 263071 AND 263071750 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD="align: left"]OPTION 263071, 263071750 ISLANDS *******[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD="align: left"]OPTION 263071750 PREMIER 4FT EXTENSION W GOURMET *******[/TD]
[TD]11[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD="align: left"]OPTION 263071750 PREMIER 4FT EXTENSION W GOURMET *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD="align: left"]OPTION 263072 AND 263072750 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD="align: left"]OPTION 263072, 263072750 ISLANDS *******[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD="align: left"]OPTION 263072750 MARQUEE 8FT EXTENSION W GOURMET *******[/TD]
[TD]14[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD="align: left"]OPTION 263072750 MARQUEE 8FT EXTENSION W GOURMET *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD="align: left"]OPTION 529750 NAPLES SUNROOM W GOURMET ******* *******[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD="align: left"]OPTION 750 GOURMET ******* *******[/TD]
[TD]71[/TD]
[TD]1[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD="align: left"]OPTION 750 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD="align: left"]OPTION 750, 760 ISLAND *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD="align: left"]OPTION 800, 800750, 263149, 263149750 ISLAND *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD="align: left"]OPTION 800023, 800750023 ISLAND *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD="align: left"]OPTION 800750 EXPANDED GOURMET ******* *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD="align: left"]OPTION 800750023 EXPANDED GOURMET KIT W EXPANDED *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD="align: left"]OPTION 886, 886750 ALTERNATE ISLAND *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]64[/TD]
[TD="align: left"]Replacement Credit OrgCntrlID = 11954750[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD="align: left"]Replacement Credit OrgCntrlID = 13867750[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD="align: left"]STANDARD AND 750 ISLAND *******[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD="align: left"]STANDARD AND OPT 750ISLANDS *******[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]68[/TD]
[TD="align: left"]STANDARD HALL BATH, OPTION #075 016 , OPTION #075 , Bath[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]69[/TD]
[TD="align: left"]STANDARD, #750 ISLAND *******[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD="align: left"]STANDARD, #750 , #132 171 , #132 172 ISLAND *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]71[/TD]
[TD="align: left"]STANDARD, #750 , #132 323 , #132 317 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]72[/TD]
[TD="align: left"]STANDARD, #750 , #135 , #135 750 ISLAND *******[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD="align: left"]STANDARD, #750 , #263163 ISLANDS *******[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]74[/TD]
[TD="align: left"]STANDARD, #750 , #532 , #529 , #529 750 #132 171 , #132 *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]75[/TD]
[TD="align: left"]STANDARD, #750 , #760 ISLAND *******[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]76[/TD]
[TD="align: left"]STANDARD, #750 , #760 , #132 323 , #132 171 , #132 172 IS *******[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD="align: left"]STANDARD, #750 , #812 , #812 750 ISLANDS *******[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD="align: left"]STANDARD, #760 , #750 ISLAND *******[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD="align: left"]STANDARD, #760 , #750 , #132 323 , #132 171 , #132 172 IS *******[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD="align: left"]STANDARD, 750 ISLANDS *******[/TD]
[TD]14[/TD]
[TD]1[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD="align: left"]STANDARD, 750, 027, 027750, 027760, 529, 529750 *******[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD="align: left"]STANDARD, 750, 132171, 132172 ISLANDS *******[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD="align: left"]STANDARD, 750, 263163, 760, 132171,132172 ISLANDS *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD="align: left"]STANDARD, 750, 760, 529, 529750, 132171, 132172I *******[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD="align: left"]STANDARD, 750, 812, 812750, 263094 ISLAND *******[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD="align: left"]STANDARD, 750ISLANDS *******[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD="align: left"]STANDARD, OPT 750ISLAND *******[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
 
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 Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
433
johnnyL
J
M
  • Question Question
first derivative
Replies
3
Views
829
JamesCanale
JamesCanale
S
  • Question Question
Consecutive points scored basketball
Replies
6
Views
4K
lrobbo314
lrobbo314
L
  • Question 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
1K
leanheart
L
C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
867
onlyadrafter
O

Forum statistics

Threads
1,223,977
Messages
6,175,753
Members
452,667
Latest member
vanessavalentino83

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