If function

[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.

--
Thanks for your help!

Walter

[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

[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.
--
Thanks for your help!

Walter


"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=534652
>
>

[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

[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

[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



--
Thanks for your help!

Walter


"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=534652
>
>

[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!!!
--
Thanks for your help!

Walter


"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=534652
>
>

[SteveG]

Walter,

Glad you got it.

Cheers,

Steve