SOLVED

How to conditionally format a cell that has two numbers instead of one?

Copper Contributor

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?

12 Replies
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.

@npshibzk 

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.

You are correct, the numbers are buried within text. I will upload some data for better reference. @mathetes 

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.

clipboard_image_0.png

@mathetes 

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

  1. 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.
  2. 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. 

 

clipboard_image_0.png

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!

@mathetes 

@npshibzkSo you actually DO have meter number as a column of its own already (although I'd say it's ten digits long, given the "100nnnnnnn" format). And there are more than a few entries under the "Remarks" column where there is no reference to meter number. In your sample, unless I missed it (entirely possible), I didn't see any that referred to more than one meter.

 

My question now is: How important is it that you identify the remarks that refer to more than one? What management or business purpose is served by those remarks other than documentation that a call was made?

 

I'm racking my brain to see if I can think of a way to re-think the function of reporting these service calls that would prevent the problem from happening in the first place. For now, even though there are 11,000 entries, it seems to me that eye-balling and fixing would be warranted.

best response confirmed by npshibzk (Copper Contributor)
Solution

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

 

You are very welcome. It's fun to take on these challenges, and it's great to actually come to a solution that works.
1 best response

Accepted Solutions
best response confirmed by npshibzk (Copper Contributor)
Solution

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

 

View solution in original post