Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Suggestion:
in another row you have the following formula per line:
=if(right(A1;1)="6";"";A1)
Then there will be an entry only when the text refers to 2006,
otherwise this cell appears to be empty. After sorting the data
according to this column you will get the desired result.

Good luck
Udo

Use AutoFilter:
Contains non


This will display rows containing both Non and non
This combined with Udo's suggestion will give you the desired result.
--
Gary's Student
gsnu200708


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Try this:

=AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("2 006",A1)))
and drag through the column. Would show TRUE in case the text contains both
NON and 2006.

-Ibrahim


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Dear Udo, thanks for your input. However, your solution I think assumes
that the year digit will always be in the last position or at least
consitently placed. What I have here is some 500 entris and some 100 people
inputing this field. You won't believe how many variation of the wording and
style occurs here. So I need a more clever formula .

Ibrahim solution is getting closer to what I need I thnik?

Best regards and thanks for your input.

Speedy.

"Udo" wrote:

Suggestion:
in another row you have the following formula per line:
=if(right(A1;1)="6";"";A1)
Then there will be an entry only when the text refers to 2006,
otherwise this cell appears to be empty. After sorting the data
according to this column you will get the desired result.

Good luck
Udo

Dear Gary, Thanks for your reply.

A little presetion, the non in the query is not referring to non recurring
in the text. I am trying to identify what are non 2006 item primarily.

Ibrahim suggestion appears closer to what I need?

It would also be nice to be able to do this on multiple criterias as the
data is the files (some 5000 entries) is very inconsistent in style and
format, eg dates in mm/dd/yy or dd/mm/yy etc.

Thanks for your input.

Speedy.

"Gary''s Student" wrote:

Use AutoFilter:
Contains non


This will display rows containing both Non and non
This combined with Udo's suggestion will give you the desired result.
--
Gary's Student
gsnu200708


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Dear Ibrahim,

Thanks for your reply. Your suggestion seems to be the closest to what I
need. I will experiment with it.

What would you suggest to handle the fact that for e.g. Non Recurring or the
dates can be found in many different style (non Rec, Non Rec, Non Recurring,
dd/mm/yy, MM/dd/yy, etc....)?

Thanks for your help.
Speedy.

"Ibrahim" wrote:

Try this:

=AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("2 006",A1)))
and drag through the column. Would show TRUE in case the text contains both
NON and 2006.

-Ibrahim


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Addendum: The dates can also be written as YYYY or YY i.e not consistent
with number of digits also.

"Speedy" wrote:

Dear Ibrahim,

Thanks for your reply. Your suggestion seems to be the closest to what I
need. I will experiment with it.

What would you suggest to handle the fact that for e.g. Non Recurring or the
dates can be found in many different style (non Rec, Non Rec, Non Recurring,
dd/mm/yy, MM/dd/yy, etc....)?

Thanks for your help.
Speedy.

"Ibrahim" wrote:

Try this:

=AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("2 006",A1)))
and drag through the column. Would show TRUE in case the text contains both
NON and 2006.

-Ibrahim


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Hi Speedy,

AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("06 ",A1)),ISNUMBER(SEARCH("*rec*",A1)))
Is this what you are looking for. Let me know.

-Ibrahim

"Speedy" wrote:

Addendum: The dates can also be written as YYYY or YY i.e not consistent
with number of digits also.

"Speedy" wrote:

Dear Ibrahim,

Thanks for your reply. Your suggestion seems to be the closest to what I
need. I will experiment with it.

What would you suggest to handle the fact that for e.g. Non Recurring or the
dates can be found in many different style (non Rec, Non Rec, Non Recurring,
dd/mm/yy, MM/dd/yy, etc....)?

Thanks for your help.
Speedy.

"Ibrahim" wrote:

Try this:

=AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("2 006",A1)))
and drag through the column. Would show TRUE in case the text contains both
NON and 2006.

-Ibrahim


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy

Hello Ibrahim,

I think this will do the trick. The formula can easely be added to for
additional search criteria and is flexible enough.

Thanks for your input.

Regards,

Speedy

"Ibrahim" wrote:

Hi Speedy,

AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("06 ",A1)),ISNUMBER(SEARCH("*rec*",A1)))
Is this what you are looking for. Let me know.

-Ibrahim

"Speedy" wrote:

Addendum: The dates can also be written as YYYY or YY i.e not consistent
with number of digits also.

"Speedy" wrote:

Dear Ibrahim,

Thanks for your reply. Your suggestion seems to be the closest to what I
need. I will experiment with it.

What would you suggest to handle the fact that for e.g. Non Recurring or the
dates can be found in many different style (non Rec, Non Rec, Non Recurring,
dd/mm/yy, MM/dd/yy, etc....)?

Thanks for your help.
Speedy.

"Ibrahim" wrote:

Try this:

=AND(ISNUMBER(SEARCH("Non",A1)),ISNUMBER(SEARCH("2 006",A1)))
and drag through the column. Would show TRUE in case the text contains both
NON and 2006.

-Ibrahim


"Speedy" wrote:

Hi, I have the following problem with data that looks like the below:

A1. "Non Rec 2004"
A2. "Non Recurring 2005"
A3. "Non Rec 1 12 2006"
A4. "non Rec 2006"
A5. "Non Recurring 2006"
A6. "Recurring 2005"

What I am trying to do is to identify in this list what are the non 2006
items with text data that is not consistently formated. The aim is to obtain
a list of the items which are related to 2006, 2005, 2004 etc. also.

Any suggestions?

Thanks Speedy