Forum Discussion
A way to filter gibberish Emails and Usernames
Thankyou NikolinoDE! Is there a way to make it also check for gibberish names that dont have numbers in them like dxus or gjdfvxbtgvxcg?
To check for gibberish names that don't have numbers, you can modify the formula to include a condition for names without numbers.
Here is an updated formula:
=IF(
AND(
OR(ISNUMBER(SEARCH("^[A-Za-z]{3,}[0-9]{4,}$", A2)), ISNUMBER(SEARCH("^[A-Za-z]{2,}[0-9]{4,}$", B2))),
NOT(OR(ISNUMBER(SEARCH("[0-9]", A2)), ISNUMBER(SEARCH("[0-9]", B2))))
),
"Potential Gibberish",
"Normal"
)
Explanation:
- OR(ISNUMBER(SEARCH("^[A-Za-z]{3,}[0-9]{4,}$", A2)), ISNUMBER(SEARCH("^[A-Za-z]{2,}[0-9]{4,}$", B2))): Checks if either the email or username follows the pattern of starting with letters and ending with numbers.
- NOT(OR(ISNUMBER(SEARCH("[0-9]", A2)), ISNUMBER(SEARCH("[0-9]", B2)))): Checks if neither the email nor the username contains any numbers.
- AND(..., ...): Checks if both conditions are true.
If both conditions are true, it returns "Potential Gibberish"; otherwise, it returns "Normal".
This formula now considers accounts with names that don't have numbers as potential gibberish. Adjustments can be made based on your specific observations and patterns of gibberish names.
- For either version I cannot seem to get it to "trigger" it to mark an account as "Potential Gibberish". For example the following variations I have tried:
EMAILS (Assume there is some sort of domain after the @):
kouoi26@
cdsa@
adsvgabaw@
gregfdf651dhedr@
jsrtdgftf23@
Usernames:
Nm1234
J23w2365
random_name1
I tried brute forcing a change to "Potential Gibberish" but nothing I did changed it. Is there something I am missing?It looks like the pattern might need some adjustments to better match the criteria you're looking for. The key is to refine the regular expression to capture the patterns you consider as potential gibberish. Based on the examples you provided, here's a modified formula:
This formula checks for two patterns:
=IF( AND( OR(ISNUMBER(SEARCH("^[A-Za-z]{2,}[0-9]{1,}@[a-zA-Z]+\.[a-zA-Z]+$", A2)), ISNUMBER(SEARCH("^[A-Za-z]{1,}[0-9]{1,}$", B2))), NOT(OR(ISNUMBER(SEARCH("[0-9]", A2)), ISNUMBER(SEARCH("[0-9]", B2)))) ), "Potential Gibberish", "Normal" )- For Emails:
- Starts with at least 2 letters.
- Followed by at least 1 number.
- Contains the "@" symbol.
- Followed by at least one letter for the domain.
- Followed by a period "." and at least one more letter for the domain.
- For Usernames:
- Starts with at least 1 letter.
- Followed by at least 1 number.
This formula now accounts for the email structure with a domain part and should flag cases that don't match these patterns as "Potential Gibberish."
You may need to further refine the patterns based on your specific data characteristics. Feel free to experiment and adjust the regular expressions to match your requirements. Regular expressions can sometimes be a bit tricky, and trial and error is often needed to get the exact match you're looking for.