Search Substring for data that shouldn't be on Spreadsheet

[Chris Waller]

Hi, I am trying to come up with an easy way in Excel 2010 to search column F for data that should not be there. Column F contains a string of data which contains a number. This number refers to the Job that has been undertaken, for Example A3 - 1G 123456 Data String. I know that the number 123456 (Always a six digit number) is correct whereas if the number was anything other than this, then that would be incorrect. There are anything from one line of data to many hundreds and I need an easy way to identify the incorrect ones. Thanks in advance.

 

[Coding4Fun]

You could use this formula

=IF(LEFT(RIGHT(F1,7),1)<>" ","Incorrect","Correct")

Put this into an adjacent cell and then copy it down for each row. If the 7th value from the right is not a blank space then it will say this is incorrect. As you stated the number is always 6 digits so the 7th should be a blank space which separates the data and the number.

 

[Coding4Fun]

Ok it looks like that would work kind of.

It has the potential to give you a false positive though.

Example if you have Tes t4 123 this would result as correct as it is finding a blank space between the s and t in Test.

 

[Rick Rothstein]

Column F contains a string of data which contains a number. This number refers to the Job that has been undertaken, for Example A3 - 1G 123456 Data String. I know that the number 123456 (Always a six digit number) is correct whereas if the number was anything other than this, then that would be incorrect.

Some questions for clarification...

1. What are we checking that any six-digit number (no matter what the digits) exists in each cell or that a specific six-digit number exists in each cell? If a specific one, where is it located at?

2. Is the structure of your data always as shown... two characters followed by a space followed by a dash followed by a space followed by your six digit number?

3. Was "Data String" meant to show some random amount of words following your six-digit number?

 

[Chris Waller]

Rick,

Question 1. I am checking that a specific number exists or rather checking that they are consistent. I need to know where any anomalies exist and I need to know these. The data will be in column F from F2 to the end of the data. The data may be one line to a couple of hundred. This is an unknown

Question 2. Now that I am at work I can confirm that the data is in the following format. A4-1 followed by a 6 digit number and some random text, or A6-16 followed by a 6 digit number and some random text, or A3-1 followed by a 6 digit number and some random text.

Question 3. Data string is supposed to represent some random words following a six digit number as you suggest. Thanks Again.

It might be useful if this were in VBA as each file I will be working on will have a different 6 digit number that will be known to me. The purppose is to check for any data that shouldn't be on the spreadsheet.

 

[Rick Rothstein]

Rick,

Question 2. Now that I am at work I can confirm that the data is in the following format. A4-1 followed by a 6 digit number and some random text, or A6-16 followed by a 6 digit number and some random text, or A3-1 followed by a 6 digit number and some random text.

What about spaces...

1) Are there spaces around the dash (like you showed originally) or not (like you are now showing?

2) Is there a space in front of and behind the six digit number?

 

[Chris Waller]

FAO Coding4Fun,

Now that I am at work I can give you the exact format the data is in, The data starts in cell f2 to the end (this is an unknown) it may be 1 row to a couple of hundred rows. The format of the data is A4-1 followed by a 6 digit number and some random text (the length is indeterminate), or A6-16 followed by a 6 digit number and some random text, or A3-1 followed by a 6 digit number and some random text. I hope this helps. Currently the formula provided just shows incorrect. Each file I receive should only contain the same six digit number. What I am trying to do is find any where there is a difference. (The six digit number will be known to me and I need to highlight any differences).

 

[Rick Rothstein]

The format of the data is A4-1 followed by a 6 digit number and some random text (the length is indeterminate), or A6-16 followed by a 6 digit number and some random text, or A3-1 followed by a 6 digit number and some random text.

Did you see the questions I asked in Message #6 yet?

 

[Chris Waller]

Rick,

No there are no spaces at all around the dash.
Yes there are spaces around the six digit number.

Thanks again.

 

[Rick Rothstein]

No there are no spaces at all around the dash.
Yes there are spaces around the six digit number.

Okay, good... so basically you want to see if the second "word" in your 6-digit number. This formula will test for that..

=MID(F1&" ",FIND(" ",F1&" ")+1,7)="123456 "

Note that your 6-digit number is included within the quotes along with a single space character trailing it (that is important).