Find numbers in a string at a certain position

G

gilbonbaggins

New Member
Joined
Oct 8, 2018
Messages
3
Howdy everyone :)

I was hoping to get some help with a situation I'm stuck with. I have an excel worksheet with data in one of the columns that looks like the following. I'm looking for an expression to find all data that has "Reads" less than 1000 (the highlighted values marked in bold).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reads: 54 (54 seek) Writes:32,133,324[/TD]
[/TR]
[TR]
[TD]Reads: 734,202 (726,337 seek 1 scan 7,864 lookup) Writes:32,133,324[/TD]
[/TR]
[TR]
[TD]Reads: 3,562 (3,562 seek) Writes:32,133,324[/TD]
[/TR]
[TR]
[TD]Reads: 2,346 (2,346 seek) Writes:32,133,324[/TD]
[/TR]
[TR]
[TD]Reads: 0 Writes:32,133,324[/TD]
[/TR]
[TR]
[TD]Reads: 133 (133 seek) Writes:32,133,324[/TD]
[/TR]
[TR]
[TD]Reads: 0 Writes:0[/TD]
[/TR]
[TR]
[TD]Reads: 9,965 (9,939 seek 26 scan) Writes:14,644,752[/TD]
[/TR]
[TR]
[TD]Reads: 55 (55 seek) Writes:0[/TD]
[/TR]
</tbody>[/TABLE]


Some background on what I'm trying to do:

The spreadsheet is generated using ImportExcel PowerShell module (https://github.com/dfinke/ImportExcel). So far the only thing I cannot seem to figure out is how to add a conditional formatting which looks like something like this.

Code: 
Add-ConditionalFormatting -WorkSheet $sheet -Range "E1:E1048576" -RuleType Expression -ConditionValue '=(NUMBERVALUE(MID(E1:E1048576,8,11))<1000)' -BackgroundColor Yellow

I'm sure my expression =(NUMBERVALUE(MID(E1:E1048576,8,11))<1000) is incorrect, and I was hoping to get some pointers to figure that part out

Thank you.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Welcome to the MrExcel board!

I've done this manually with Conditional Formatting but if it is what you want, you should be able to apply it through vba if you want, or post back for more detailed help.

<b>Reads</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:446px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Data</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; font-size:10pt; ">Reads: 54 (54 seek) Writes:32,133,324</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Reads: 734,202 (726,337 seek 1 scan 7,864 lookup) Writes:32,133,324</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Reads: 3,562 (3,562 seek) Writes:32,133,324</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Reads: 2,346 (2,346 seek) Writes:32,133,324</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#92d050; font-size:10pt; ">Reads: 0 Writes:32,133,324</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#92d050; font-size:10pt; ">Reads: 133 (133 seek) Writes:32,133,324</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#92d050; font-size:10pt; ">Reads: 0 Writes:0</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">Reads: 9,965 (9,939 seek 26 scan) Writes:14,644,752</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; font-size:10pt; ">Reads: 55 (55 seek) Writes:0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >A2</td><td >1. / Formula is =ISERROR(FIND(",",LEFT(A2,FIND("(",A2))))</td><td style="background-color:#92d050; ">Abc</td></tr></table></td></tr></table> <br /><br />


Edit: Actually, if that sample data is representative, this much simpler formula in conditional formatting should also do the job.

=FIND(",",A2&",")>11
 
Last edited:
Upvote 0
Hi Peter. I really appreciate the quick response. Thank you.

I did quick manual check in a spreadsheet with a sample for =FIND(",",A2&",")>11. It looks like it returned a few false positives (highlighted values in the dropbox attachment).

https://www.dropbox.com/s/677jvgh5b8tidr8/spreadsheet-1.png

<tbody>
[TD="class: xl68"]Reads: 0 Writes:0
[/TD]
[TD="class: xl69, align: center"]FALSE
[/TD]

[TD="class: xl68"][/TD]
[TD="class: xl69, align: center"]TRUE
[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl68"]Reads: 0 Writes:0[/TD]
[TD="class: xl69, align: center"]FALSE[/TD]

[TD="class: xl66"][/TD]
[TD="class: xl67, align: center"]FALSE[/TD]

[TD="class: xl68"]Reads: 7,974 (7,974 seek) Writes:0[/TD]
[TD="class: xl69, align: center"]TRUE[/TD]

[TD="class: xl68"]Reads: 55 (55 seek) Writes:0[/TD]
[TD="class: xl69, align: center"]FALSE[/TD]

[TD="class: xl66"]Reads: 83,912 (76,166 seek 7,746 lookup) Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 35 (35 seek) Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl68"]Reads: 97 (97 seek) Writes:0[/TD]
[TD="class: xl69, align: center"]FALSE[/TD]

[TD="class: xl66"][/TD]
[TD="class: xl67, align: center"]FALSE[/TD]

[TD="class: xl66"]Reads: 15,844 (15,844 seek) Writes:0[/TD]
[TD="class: xl67, align: center"]FALSE[/TD]

[TD="class: xl68"]Reads: 30,877 (30,875 seek 2 scan) Writes:0[/TD]
[TD="class: xl69, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 164 (164 seek) Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 13 (13 seek) Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl66"]Reads: 0 Writes:0[/TD]
[TD="class: xl67, align: center"]TRUE[/TD]

[TD="class: xl68"]Reads: 0 Writes:0[/TD]
[TD="class: xl69, align: center"]FALSE[/TD]

</tbody>

 
Upvote 0
gilbonbaggins said:
I did quick manual check in a spreadsheet with a sample for =FIND(",",A2&",")>11. It looks like it returned a few false positives (highlighted values in the dropbox attachment).
Click to expand...
It appears that the formula shown is in cell B7 but references A8. :)
Fix that & test again.

If you are going to use dropbox, it would be better to use an actual sample file that we can test with rather than an image that we can only look at.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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