| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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",I F(ISNUMBER(SEARCH("Master Card",A1,1)),"Master Card",IF(ISNUMBER(SEARCH("Discover",A1,1)),"Discov er",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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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",I F(ISNUMBER(SEARCH("Master Card",A1,1)),"Master Card",IF(ISNUMBER(SEARCH("Discover",A1,1)),"Discov er",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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=534652 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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",I F(ISNUMBER(SEARCH("Master Card",A1,1)),"Master Card",IF(ISNUMBER(SEARCH("Discover",A1,1)),"Discov er",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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
Walter, Glad you got it. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=534652 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=534652 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
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? -- Thanks for your help! Walter "SteveG" wrote: Walter, Glad you got it. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=534652 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If function
Walter, I honestly don't use Access that much. You can not use the same code but you can build your queries to search a text string for a value. I am sure it can be done, I am just not completely familiar with how. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=534652 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
