If function

B

BARRIOSWJ

I have the following text string:
Rcpt Revsed as Remit Faild on 02/15/06. Orig Paym Detl -
PSON:BOB_23200937334 Orig Paymethd:DIR American Express C.Bnk:Credit Card
Bank Rtng#: Acct#:3732-xxxx-xxxx-xx5 Exp Date:09/30/06 Ptech mesg:Call Voice
Center.

What I want to do is an if statement that says
if the Acct # starts with a 3 label it as american express
if it starts with a 4 label it as a Visa
if it starts with a 5 label it as a Discover.

However, the only problem is that sometimes the account number appears in
the beginning of the text, sometimes at the middle and sometimes at the end.
Can someone help? Thanks.
 
S

SteveG

Try this where your text string is in A1.

=CHOOSE(MID(A1,SEARCH("Acct#",A1,1)+6,1)-2,"American
Express","Visa","Discover")

This works if your account number abbreviation is always in the same
format as posted. "Acct#:"

Does that help?

Steve
 
B

BARRIOSWJ

Hi Steve!
Results come back great for American Express, but my master cards that
begin with a 5 are coming back with either visa or Discover. That part is
incorrect.
 
S

SteveG

Ah. You didn't say that you had another card company in your OP. If
all of your text strings contain the name of the credit card company
within it somewhere then you could use this:


=IF(ISNUMBER(SEARCH("American Express",A1,1)),"American
Express",IF(ISNUMBER(SEARCH("Visa",A1,1)),"Visa",IF(ISNUMBER(SEARCH("Master
Card",A1,1)),"Master
Card",IF(ISNUMBER(SEARCH("Discover",A1,1)),"Discover",0))))


Does that help?

Steve
 
B

BARRIOSWJ

The following text string is in column Z. When I try your formula it still
does not work for some of the CC types.

Rcpt Revsed as Remit Faild on 04/14/06. Orig Paym Detl -
PSON:BOB_21201653350 Orig Paymethd:DIR American Express C.Bnk:Credit Card
Bank Rtng#: Acct#:378300000000007 Exp Date:05/31/06 Ptech mesg:processor
Decline

Rcpt Revsed as Remit Faild on 04/14/06. Orig Paym Detl -
PSON:BOB_31201652172 Orig Paymethd:DIR MasterCard C.Bnk:Credit Card Bank
Rtng#: Acct#:5490000000000000 Exp Date:10/31/06 Ptech mesg:Over Freq Limit

Rcpt Revsed as Remit Faild on 04/11/06. Orig Paym Detl -
PSON:BOB_13201523368 Orig Paymethd:DIR Visa C.Bnk:Credit Card Bank Rtng#:
Acct#:4388000000000000 Exp Date:04/30/07 Ptech mesg:Do Not Honor

Rcpt Revsed as Remit Faild on 10/04/05. Orig Paym Detl - PSON:BOB_20782906
Orig Paymethd:DIR Discover C.Bnk:Credit Card Bank Rtng#:
Acct#:6011000000000000 Exp Date:03/31/08 Ptech mesg:Do Not Honor
 
S

SteveG

One other thing. You said that your master cards that begin with a 5
are coming back as either a visa or discover. It should always be
discover. The CHOOSE function uses the MID and SEARCH functions to
return an index to then find it's corresponding value within the
function so -

=CHOOSE(MID(A1,SEARCH("Acct#",A1,1)+6,1)-2

This creates the index. It finds the text "Acct#" returns its starting
position number and adds six characters to find the position of the
account #. The MID then returns the value of that number (3,4 or 5 in
your scenario). By subtracting the 2 this changes the index number to
1,2,3 respectively. The CHOOSE will then pick left to right in
ascending order of index values from your text strings within the
formula or

1= "American Express", 2 = "Visa" and 3 = "Discover". So you see, no
matter what your credit card name is, if the account # begins with a 5
the index is then 5-2 or 3 which should always return "Discover". If
you have different card companies that can start with the same account
number, this obviously won't work. Hopefully the other formula will
work for you.

Regards
Steve
 
B

BARRIOSWJ

I got it!!!! I just had to remove the space for Master Card. Your formula
was with a space but my text string comes without a space. Removed the space
and now I have them all properly. Thanks a million!!!
 
S

SteveG

Walter,

I did not see your post with the sample data. If the different cc
company accounts always start with a unique number you can still use
the original formula I posted. It looks like your breakdown is like
this:

3 = AMEX
4 = Visa
5 = MC
6 = Discover

Change the formula to this.

=CHOOSE(MID(A1,SEARCH("Acct#",A1,1)+6,1)-2,"American
Express","Visa","MasterCard","Discover")

If I am wrong in my assumption, you can use the other formula.

Cheers

Steve
 
B

BARRIOSWJ

Technically you got for me. I just happen to stumble on that piece of it.
One last question. Will this work for Access as well?
 
S

SteveG

Walter,

I honestly don't use Access that much. You can not use the same cod
but you can build your queries to search a text string for a value.
am sure it can be done, I am just not completely familiar with how.

Regards,

Stev
 
Top