Filtering by Term Digits

[christina]

I'm a TL at an attorneys office and our vendor file numbers are in the
following format:

VEN-0111223

I deal with large spreadsheets that I was manually splitting up and
delegating to associates by filtering and giving myself major headaches
(not to mention taking up most of my morning just giving them work.
Thank you Tech department from hell). I'm in the process of trying to
macro it out to where I come in and refresh the data with all of the
new files for the day and have a macro split up their work into their
specific portfolio assignments.

The problem I'm running into is that I have associates assigned by
client then by ODD and EVEN VEN numbers then further split by client.
The client split is easy but identifying odd and even text/numeric
fields is proving to be difficult. All of the formulas I found on the
forum are for number fields only. I need to be able to take a
spreadsheet full of VEN-0111223 and split them out by odd's and even's.
I've used the =ISODD(C11) function but it only works on the loan number
fields and their work is driven by the VEN numbers.

Can anybody here help me out with this lovely task? I would be a very
happy camper.

 

[Pete_UK]

If your file numbers are always of the form VEN-number, then the
following formula will return "odd" or "even", assuming the data is in
A1:

=IF(MOD(RIGHT(A1,LEN(A1)-4),2),"odd","even")

Is this something you can work with?

Hope this helps.

Pete

 

[christina]

That works great! i forgot to also take into consideration when we have
multiple files for the same account our file numbers add an A or a B to
the end of the file number. for example

VEN-00111223A
VEN-00111223B

I am receiving the dreaded #VALUE! on these fields.

 

[Dave Peterson]

And just in case you see A as odd and B as even (yeah, not much change <bg>).

=ISODD(code(RIGHT(C11,1)))

=isodd() is part of the analysis toolpak addin.