Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to check for valid and invalid email addresses in my data, please see formula done below.
I am however still getting data data should not be in the valid field.
If(Len(IDNumber) <> 13 Or Right(IDNumber, 7) = '0000000' Or Right (IDNumber, '/') Or Right (IDNumber, 'NPO' Or Right(IDNumber, '()')), 'Invalid', 'Valid') as [IDnumber Valid],
Sample Data:
0000/00/00000
NPO/000/0000
0000/00/000(00)
If this resolved, Please close this thread.
Sorry not email address, ID numbers.
expected result?
I would like to see what is a valid ID number and what not.
I am not sure if you have used the right() function in the correct way.
Can you post the logic you need and some sample data?
Do you think this condition, Correct. For me it is quite wrong. As per your logic, Output will come like
Sample Data:
0000/00/00000 -------------> Len(IDNumber) <> 13 Or Right(IDNumber, 7) = '0000000' --------> True first part, Hence it will return 'Invalid'
NPO/000/0000 -----------> True --> Because, It is not the 13 Length --> Should return "Invalid"
0000/00/000(00) ---> true --> Because, It is not the 13 Length --> Should return "Invalid"
Below part is wrong for me. Can you explain what you are trying using these condition here?
Right (IDNumber, '/') Or Right (IDNumber, 'NPO' Or Right(IDNumber, '()'))
Sample Data
I would like to check in my data, which of the ID numbers are qualifying as a valid ID number, but i have special characters in of which when checking they are not a valid ID number.
ID Number | Len | Reason |
8106075006000 | 13 | Valid |
8106075006111 | 13 | Valid |
8111175006000 | 13 | Valid |
811(175006000 | 13 | Invalid |
811(175NPO | 10 | Invalid |
8111175//000 | 12 | Invalid |
0/// | 4 | Invalid |
20016/0111/11 | 13 | Invalid |
Try this?
If(Len([ID Number])=13 and not FindOneOf([ID Number], '!@#$%^&*()/\_+-={}[]:;"''<>,./?~`'), 'Valid', 'Not valid')
If this resolved, Please close this thread.