Find numbers in a string at a certain position

[gilbonbaggins]

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

Reads: 54 (54 seek) Writes:32,133,324

Reads: 734,202 (726,337 seek 1 scan 7,864 lookup) Writes:32,133,324

Reads: 3,562 (3,562 seek) Writes:32,133,324

Reads: 2,346 (2,346 seek) Writes:32,133,324

Reads: 0 Writes:32,133,324

Reads: 133 (133 seek) Writes:32,133,324

Reads: 0 Writes:0

Reads: 9,965 (9,939 seek 26 scan) Writes:14,644,752

Reads: 55 (55 seek) Writes:0

<tbody>
</tbody>

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.

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.

 

[Peter_SSs]

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

 

[gilbonbaggins]

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

Reads: 0 Writes:0	FALSE
	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	FALSE
	FALSE
Reads: 7,974 (7,974 seek) Writes:0	TRUE
Reads: 55 (55 seek) Writes:0	FALSE
Reads: 83,912 (76,166 seek 7,746 lookup) Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 35 (35 seek) Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 97 (97 seek) Writes:0	FALSE
	FALSE
Reads: 15,844 (15,844 seek) Writes:0	FALSE
Reads: 30,877 (30,875 seek 2 scan) Writes:0	TRUE
Reads: 164 (164 seek) Writes:0	TRUE
Reads: 13 (13 seek) Writes:0	TRUE
Reads: 0 Writes:0	TRUE
Reads: 0 Writes:0	FALSE

<tbody>
</tbody>

 

[Peter_SSs]

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

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.

 

[gilbonbaggins]

Rookie mistake. My bad

Thanks.

 

[Peter_SSs]

Rookie mistake. My bad

Thanks.

Easy to do. Glad it is resolved now.