Identify incorrect format

[darkhangelsk]

Hi,

I would like to ask if there's a fastest way to identify incorrect format? like in column A there's a hundreds - thousands of data and i have to make sure they have correct format.
Correct format would be:
TICKET-12345 - (any text here)

So basically i need to identify the incorrect ones. Some are like this - > "TICKET_12344 text" or TICKET-12244 text
for these kind of format i need to highlight it.

Identifying it manually making me sick and eat lot of time. But if someone can help correct direction, that would be great.

Thanks!

 

[JackDanIce]

One quick suggestion is to select the data then Data->Text to Columns, go through the wizard and choose delimiter character "-" then review the results

 

[KRice]

Another option to consider. Use a regular filter (under Sort & Filter). In the drop down that will be added to the column heading, select Custom Autofilter and then choose the option to show rows where item "does not begin with" and then enter "TICKET-12345-" in the field. Your list will be filtered to show only those items that require further attention. When done, just clear the filter in the Sort & Filter section.

 

[darkhangelsk]

Thank you for the suggestion. but it is not working. I think i'm not clear regarding the format.

These are sample correct format:
TICKET-12345 - The cellphones
TICKET-12346 - My name
TICKET-11111 - CAPTURED TICKETS
TICKET-00000 - your name

Ticket number could be any but the spacing and format should be the same. The instruction that were give to them is like this:

creating ticket should be:
"TICKET-##### - (content)"

So i need to capture incorrect naming/formatting on that column.

Thanks.

 

[JackDanIce]

You can specify the deliminating character when you use Text to Columns, so you could try " - " and then evaluate the results.

 

[KRice]

OK, that is slightly different than my interpretation of your first post. So any five digit number following "TICKET-" is okay...and then that five digit number is followed by " - " (a hyphen surrounded by one space), and then any text can appear at the end of the string. Are the spaces around the last hyphen important? JackDanice offers a nice approach if you don't care about the spaces around the hyphen...just make a duplicate copy of the column you want to examine, and then insert several more columns to receive the broken apart text strings (so that you don't overwrite any existing data).

 

[denzo36]

Hey - can I clarify should your format always be 'TICKET - ##### - some text' i.e always the word "TICKET" first followed by space,a dash,space followed by a 5 digit number followed by space dash then some text and this format will never change?

 

[darkhangelsk]

Thank you for the replies. JackDanice offers is almost same as doing it manually, because i need to check every rows.

i was hoping if there's a code or maybe validation that will highlight the incorrect formats so i can just filter it. that would be awesome.

 

[Fluff]

How about using conditional formatting

+Fluff.xlsm
	A
1
2	TICKET-12345 - The cellphones
3	TICKET-1234 - My name
4	TICKET -11111 - CAPTURED TICKETS
5	TICKEt-00000 - your name
6	TICKET-12345 - The cellphones
7	TICKET_12345 - My name
8	TICKET-11111 -CAPTURED TICKETS
9	TICKET-00000 - your name
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=NOT(AND(EXACT(LEFT(A2,7),"TICKET-"),ISNUMBER(MID(A2,8,6)+0),MID(A2,13,3)=" - "))	text	NO

 

[darkhangelsk]

Thank you Fluff! this is exactly what i need. Also big thank to denzo36, KRice and JackDanice for the time and trying to solve my concern. Really appreciate it!