# Using match and left function to detect fraud

1. ## Using match and left function to detect fraud

Hi I'm doing accounting homework but stuck here.

SSN
345-88-9877
913-57-8024
024-56-8976

Assume SSN that begins with digit 9 is invalid. I need to detect the invalid SSNs. If the ssn is invalid then display "invalid", or, leave it blank.
I was using this formula: =if(isna(match(9,left(a2,1),0))," ","invalid") but excel tell me I'm wrong. Can you help me?

2. ## Re: Using match and left function to detect fraud

Try

=if(left(a2,1)="9","invalid","")

Match() is used to find a value in a range of cells, not a character in a single cell. If you only want to inspect the first character, then Left() is the function you need.

cheers, teylyn

3. ## Re: Using match and left function to detect fraud

Try this one

=IF(COUNTIF(A1,"9"&"*"),"Invalid","Ok")

 Row\Col A B 1 345-88-9877 Ok 2 913-57-8024 Invalid 3 024-96-8976 Ok

4. ## Re: Using match and left function to detect fraud

@AlKey, CountIf() is designed to be used on a range of cells. Although you can limit the range to a single cell, there are better ways to do that. To inspect a single cell, string functions that operate on a single cell like Left(), Right(), Mid(), Find(), Search(), etc are a better fit.

5. ## Re: Using match and left function to detect fraud

Originally Posted by teylyn
@AlKey, CountIf() is designed to be used on a range of cells. Although you can limit the range to a single cell, there are better ways to do that. To inspect a single cell, string functions that operate on a single cell like Left(), Right(), Mid(), Find(), Search(), etc are a better fit.
I would disagree.

6. ## Re: Using match and left function to detect fraud

=IF(LEFT(M16,1)="9","Invalid","") it works!

If I want to detect an ssn that is either begins with digit 9 OR has 00 on the middle, what formula should I apply?

7. ## Re: Using match and left function to detect fraud

=IF(OR(LEFT(A1)="9",MID(A1,5,2)="00"),"Invalid","Ok")

8. ## Re: Using match and left function to detect fraud

Try

=IF(LEFT(A2,1)="9","invalid",IF(ISNUMBER(FIND("00",A2)),"invalid",""))

The first part of the IF statement checks for a 9 as the first character. If the first character is a 9, then "invalid" will be returned. If the first character is not a 9, another IF statement checks with the FIND() function whether the string contains two zeros in a row. FIND() returns a number or an error. So if a number is returned, the value is found. Therefore, we wrap the FIND() function in and ISNUMBER() function which will return TRUE or FALSE, depending on the result that FIND returns.

This will find two zeros anywhere in the cell. If you are after a specific position only, then the Mid() function approach might be better.

2014-11-23_14-22-47.png

9. ## Re: Using match and left function to detect fraud

Originally Posted by AlKey
I would disagree.

10. ## Re: Using match and left function to detect fraud

Thank you Finally I can finish my homework! you guys saved my weekend!

11. ## Re: Using match and left function to detect fraud

You're welcome and thank you for the feedback!

12. ## Re: Using match and left function to detect fraud

Originally Posted by teylyn
I disagree with the idea that a function that works over the range of cells is less effective when applied to one cell. In fact, use of COUNTIF(s) with text strings sometimes can be only option to accomplish the task.

13. ## Re: Using match and left function to detect fraud

That's interesting. I'd like to learn more about such scenarios. Do you have an example at hand? Might make a good article on what function to use in what situation.

14. ## Re: Using match and left function to detect fraud

Originally Posted by teylyn
That's interesting. I'd like to learn more about such scenarios. Do you have an example at hand? Might make a good article on what function to use in what situation.
I will PM you

15. ## Re: Using match and left function to detect fraud

Using the calculation timer code found here:

http://msdn.microsoft.com/en-us/library/aa730921.aspx

Tested on my machine in Excel 2002 the =IF(LEFT(A2,1)="9","invalid","") formula was faster to calculate.

=IF(LEFT(A2,1)="9","invalid","") average time to calculate 5 iterations: 0.000128 sec

=IF(COUNTIF(A2,"9"&"*"),"Invalid","Ok") average time to calculate 5 iterations: 0.000172

16. ## Re: Using match and left function to detect fraud

A couple of minor tweaks...

=IF(LEFT(A2,1)="9","invalid","")
=IF(LEFT(A2)="9","invalid","")

=IF(COUNTIF(A2,"9"&"*"),"Invalid","Ok")
=IF(COUNTIF(A2,"9*"),"Invalid","Ok")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1