Valid Email Addresses

[mcoetzee]

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)

[mcoetzee]

Sorry not email address, ID numbers.

[Anil_Babu_Samineni]

expected result?

[mcoetzee]

I would like to see what is a valid ID number  and what not.

[arpitkharkia]

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?

[Anil_Babu_Samineni]

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, '()'))

[mcoetzee]

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

[Anil_Babu_Samineni]

Try this?

If(Len([ID Number])=13 and not FindOneOf([ID Number], '!@#$%^&*()/\_+-={}[]:;"''<>,./?~`'), 'Valid', 'Not valid')

[Anil_Babu_Samineni · Accepted solution]

If this resolved, Please close this thread.