Need a Formula to check format of Specific data

[Jac_Perdue]

Conditional Formatting cells for text

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am new to this so i apologize if this is already posted... What i am looking to do is conditional format a column to look for several things. <o></o>
<o></o>
1) most important is that the text is in a specific format (order). example of the format is - G0E1111JJB2L00KL,001510206F7,1100HH003029<o></o>
<o></o>
2) that the items in the cells are not duplicated. this i can do as long as the data is in the correct format.<o></o>
<o></o>
if the cell violates either of these rules i would like it to high light bright red<o></o>
<o></o>
i can set up the cells to see that the data is not duplicated but i am having trouble trying to set them up so that the formatting (order) is correct. some of the text will be the same in all the cells where as some will be different. here is an example of the text that will be the same and what will be different. - G0E########,001######,1100###### (this is not to scale but you get the idea). <o></o>
<o></o>
is there a way for me to do what i am looking to do? <o></o>

 

[Jac_Perdue]

Conditional Formatting cells for text

I should have stated that i am useing excel 2010 on an Windows XP machine

 

[Jac_Perdue]

Hello All, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
i am looking for a formula to check the formatting of specific data in a column. the format of the data will be entered by a barcode and will need to be verified that it is not duplicated (ok) but will also need to be verified that it is in the correct format i.e.: TEM01223,001567,09J004 this format is similar to the way that the data is entered into the cells. i need to come up with a way that the data is verified in-between the commas. <o></o>
<o></o>
i was thinking using the first 3 digits between the commas (that information doesn’t change) could be used to determine if the format is correct. then the conditional formatting of duplicates would verify that it is not repeated. <o></o>
<o></o>
i have tried several ways that i found on-line and now am desperate. so i am putting it to you do you all think there is a way to do what i need done? <o></o>
<o></o>
i am willing to try any suggestion. <o></o>
<o></o>
thank you for your help. <o></o>

 

[Asala42]

Can you post a few examples of "good" and "bad" code entries? Just trying to understand the pattern differences that define what's good or bad.

Do the codes have varying length? Should they all contain 22 characters with the 2 commas?

 

[Jac_Perdue]

Can you post a few examples of "good" and "bad" code entries? Just trying to understand the pattern differences that define what's good or bad.

Do the codes have varying length? Should they all contain 22 characters with the 2 commas?

I can give some examples of good and bad code i will use some real data to help me out with this. the ones highlighted in Red are Duplicate and the ones highlighted in orange are in the wrong format these are what i would consider to be bad. the 2 with out any format are ideal and what i would like the whole sheet to look like when we are done... <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
the length will always stay the same. so the first item will be 16 digits then a comma the second will be 12 digits then a comma and the third will be 12 digits. the second and third are a big concern for me as they are the same length. <o></o>
<o></o>
<TABLE style="WIDTH: 227pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=303><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 227pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=303></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in"></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #e6b8b7; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in">G0E1221JJB7L00GL,C4B5120205B6,1107JJ002029<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #e6b8b7; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in">G0E1221JJB7L00GL,C4B5120205B6,1107JJ002029<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in">G0E1221JJB7L00TD,C4B5120206E6,1107JJ002033<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in">G0E1221JJB7L00KL,C4B5120206F7,1107JJ003029<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #fabf8f; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in">1107JJ002033,G0E1221JJB7L00TD,C4B5120206E6<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #fabf8f; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in">G0E9991JJB7L99TD,1107JJ002999,C4B5120999E6<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in"></TD></TR></TBODY></TABLE>
<o> </o>
<o> </o>

 

[Joe4]

Note that since all your threads were on the same topic, I have merged them together. Please do not create multiple threads on the same topic.
All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

 

[Asala42]

If I follow your correctly, the code is bad if any of the criteria is not met:

- The combined string does not have exactly 42 characters, including the commas. (16,12,12,+2)
- The first string must start with "G0E"
- The 2nd string must start with "C4B"
- The 3rd string must start with "110"

So in your example, the last 2 entries are highlighed orange because the codes were out of order.

This is one possible conditional formatting formula:

=OR(LEN(A2)<>42,
LEFT(A2,3)<>"G0E",
MID(A2,FIND(",",A2)+1,3)<>"C4B",
(MID(RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND(",",A2)-1))-1),FIND(",",RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND(",",A2)-1))-1))+1,3))<>"110")

 

[Jac_Perdue]

If I follow your correctly, the code is bad if any of the criteria is not met:

- The combined string does not have exactly 42 characters, including the commas. (16,12,12,+2)
- The first string must start with "G0E"
- The 2nd string must start with "C4B"
- The 3rd string must start with "110"

So in your example, the last 2 entries are highlighed orange because the codes were out of order.

This is one possible conditional formatting formula:

Thank you for the formula. took me a minute to see what was going on in it but i got it. it does not seem to work for me though. from looking at it, the OR statement says that it will only give a False if ALL parts of the formula are False. so if any part of this is true it will return a True. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
i am trying to figure out how to modify it so that if any part is False the whole formula will be a False. i thought IF would work but i cannot seem to figure out the logistics of it. I tried an AND statement and got it to work except that it highlights the pervious entry after a new entry is entered. Below is a copy of the current formula I am entering into the conditional formatting <o></o>
<o> </o>
<o> </o>
=AND(LEN(A2)=42,<o></o>
LEFT(A2,3)="G0E",<o></o>
MID(A2,FIND(",",A2)+1,3)="C4B",<o></o>
(MID(RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND(",",A2)-1))-1),FIND(",",RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND(",",A2)-1))-1))+1,3))="110")<o></o>
<o> </o>