I have a list of approx 40,000 item #'s and I need to identify anything in the list that does not begin with with a 4 character set of 3 numbers and a dash (-). Here is an example of the data
369-8179
369-1412
369-6201
J5239-40W
140-PMT280
361-1626
361-1629
361-1630WD
361-1649R
SP14G
SP14O
I would need a formula that would identify the 4th/10th/11th cells
Thanks in advance for the help
_EB
=IF(MID(A1,4,1)="-", "", "X") copied down
puts an x next to every value which doens't match your pattern.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Something like:
That will evaluate to TRUE if the first 3 characters are a number AND the 4th character is a "-" The 4th, 10th, and 11th cells would be "FALSE"
Please Login or Register to view this content.
Does that work for you?
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
Hi mnfez,
try this paste it in the row with the first line of your data an it will return true or false if it matched your criteria. You will only need to adjust the "A7" address values to point to your first line of data. Then copy and paste it down the column.
JimBobBowiePlease Login or Register to view this content.
If you don't want extra formulae/columns in your data, you can highlight the 'wrong' cells with Conditional Formatting.
Click Conditional Formatting and select 'New Rule' then 'Use a formula to determine which cells to format'. Put the formula below into the formula box then choose how you want those cells highlighted (yellow fill, red text, whatever):
=FIND("-",A1)<>4
Then repeat with this formula:
=ISERROR(FIND("-",A1))
Edit:
A better formula to use in the Cond Formatting would be one of these (amended from GeneralDisarray's and JimBobBowie's so kudos to them):
=NOT(AND(NOT(ISERROR(LEFT(A16,3)*1)),MID(A16,4,1)="-"))
=NOT(AND(ISNUMBER(LEFT(A28,3)*1),MID(A28,4,1)="-"))
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
Last edited by Aardigspook; 09-01-2015 at 09:40 AM. Reason: Suggest better CF formulae
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks