Forum Discussion
How to conditionally format a cell that has two numbers instead of one?
Hello all,
I have a column that has text with activities that field technicians completed on meters. In this column, they input a meter #. However, sometimes they accidentally could input two meter #'s. I am interested in somehow conditionally formatting this column for each cell, that if they do have two meter #'s somewhere in the paragraph described, that it will flag the cell (color code, etc.). Each meter # is six digits long, so is there a way I could just search each cell, see if there exists two six digit numbers in that cell, and flag it? Or should I search each cell by text (look for "meter" twice in the paragraph, flag it)? Is this even possible?
npshibzk Just re=reading what you wrote:
I highlighted every cell that contains "METER" or "MTR" thus far to help narrow it perhaps.
Did you do that highlighting using conditional formatting? Or was that a manual thing just for this posting? If you did it yourself using conditional formatting, maybe that's your quick and dirty solution.
But still would require a lot of work. It is a mess!
Yep. But a series of manual fixes, accelerated by just searching for METER, MTR, or even #.... and you could do that using a "helper column" next to the "Remarks" column.
In fact, after writing that, I went and wrote the formula for you. Here it is. I added multiple helper columns, with a "search term" at the top.... you could extend this. As you see, it will pick up and highlight the row that contains METER, meter, MTR, mtr, #. and any combination of those..... So all you need to do is put some temporary helper columns next to your "Remarks" and copy these formulas down all 11,000 rows. It'll still be work, but that should accelerate it.
12 Replies
If each Meter# consists of 6 digits and more than one Meter# is entered in the same cell, they must be separated by a delimiter. Is it the case?
If so, you can simply check the length of the cell content and if it is greater than 12 that simply means there are more than one Meter# present in the same cell.
Assuming your Meter# are in column A, then select the whole column A (when you select the whole column A, cell A1 will be Active Cell in the Selection) and make a new rule for conditional formatting using the formula given below and set the format as per your choice.
=LEN(A1)>12
Please make sure that the cell referred in the formula must be the first cell in the range in which you are applying the conditional formatting.
e.g. if you want to apply the conditional formatting to the range D2:D100 (assuming that the Meter# are in column D where D1 is the column header), select the range D2:D100 and use the below formula for the conditional formatting...
=LEN(D2)>12
If that doesn't work as expected, please upload a small sample file so that we can suggest a working solution considering your existing data.
Subodh_Tiwari_sktneerIt's possible that one of us has mis-interpreted the orginal post by @npshibzk.
I read it to mean that the potential for two Meter Numbers is to be "buried" in a longer text entry describing work done by a technician on the meter in question. It's not just the entry of the meter number, in other words. If you are right, on the other hand, your suggestion will do the trick by highlighting the cell if it contains more than six figures.
Either way, though, both of us have asked for an uploading of an example of what @npshibzk is working on....always easier to work with an actual example.
Here is a screenshot of the column of "Remarks" where the meter numbers would be documented in. There are over 11,000 rows, which is why I was wondering if there was an easier way to flag cells that have two meter numbers written in them. Also, I apologize, but it appears meter numbers are 7 digits long.
- If it's possible to change the way the sheet is designed, why not add a column that is solely for the meter #, set it so it accepts (Data...Validation) nothing longer than six digits. That would be a more robust way to handle this in the first place.
Is there some particular reason why you use Excel for this business purpose in the first place? Does the worksheet also track hours or expenses associated with a call, for example? Assuming that is the case, it makes even more sense that the Meter Number be its own cell/column.
If you want further assistance or suggestions, might I ask that you upload a sample or two of the way it looks now, i.e., a representative spreadsheet with some filled in as you want, and one or two that are not.
