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.
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.
Each cell is a different customer/call.
Thank you for looking at this. The above screenshot shows all columns. I highlighted every cell that contains "METER" or "MTR" thus far to help narrow it perhaps. But still would require a lot of work. It is a mess!
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.
- 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?