Forum Discussion
How to conditionally format a cell that has two numbers instead of one?
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.
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.
npshibzkOh, my. What a tangled mess (if you'll excuse me). The fact that meter numbers are seven digits is not an issue. What makes this awfully difficult to sort out is that there are OTHER numbers in the same remarks (as I expected, frankly). So, although you could write a rule to get only seven digit numbers and somehow flag it, what you really need is a redesigned spreadsheet.
Some questions for you.
- In that screen shot, it's not clear if those entries all refer to a single customer, or is each cell a different customer/service call?
- If the latter, then why isn't the meter number part of each cell?
- If the former, then it's going to be even harder to sort out. Good database design would have one row (perhaps with wordwrap that allows multiple lines in the comment cell) per service call.
- What are the other columns? If there aren't any other columns, why are you using Excel in the first place? You could use Word to record comments.
I'm going to assume that there are other columns, where you record such things as "Time Spent" or other things for billing/accounting purposes. Based on that assumption, I'm going to go back to my first suggestion, which was to add a column in which Meter Number is to be entered, and set it up with Data--Validate such that only a maximum of seven digits can be entered. Yes, this will be a lot of work. Think of it as a learning experience.
I've attached a sample showing how that data validation works. If you can upload a portion of the actual spreadsheet (showing other columns as well), I'd be happy to look at it further.
- In that screen shot, it's not clear if those entries all refer to a single customer, or is each cell a different customer/service call?
Yes, I agree with you.
