Looking to find unsupported user names

[craving94509]

I have an extract of data from our user DB, where some users have registered with unformated user names e.g Admin, Admin2, dave, our test account, etc.) I am wondering if there is formula or VBA that can be run to find "non-proper" user names (first last) names. Bob Jones is okay, Bob2, Bob test user, AdminBob is not okay. Hoping someone has some ideas to comb through 20k users?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I don't think that it is entirely clear exactly what the definition of a "proper" name is for you. See if this is on the right track.
If not, then you need to clarify better what constitutes a "proper" name.

22 02 02.xlsm
	A	B
1	User name	Wrong?
2	Admin	x
3	Bob Jones
4	Bob test user	x
5	Bob test	x
6	Bob Test
7	Admin2	x
8	Fred Derf
9	Don ADAMS	x
10	Joseph Jack Smith	x
User Names

 

[Micron]

Your easiest would be to lock everybody out of the db and enforce creation of new usernames because it would be easier to prevent characters/spaces when creating new usernames rather than fixing them. So I'm saying rather than mine this data and play with it in Excel, go straight to the problem app and make the users fix it. You'll also save yourself some time and trouble for the 500 - 1000 who don't (or hardly) use the db any more.

Unless users are sharing a Windows session, there's not much value in passwords and all the maintenance that goes with it IMO. Consider just using their Windows login name, which likely has to be unique in the organization anyway? I entered the login name and user opened db. If their Windows login wasn't there, they didn't get in. Mind you, I didn't have 20K users in any db so that might not seem appealing. It would, however, force them to use those credentials since you can easily compare what they're typing in to what they should be typing in. If all Windows accounts must use a unique login, I'd set the db field to not allow dupes as well.

If none of that seems appealing, you can run sql looking for values that you've identified, but that will require you to first find the values you don't want to allow. Rather than a query with a whole bunch of OR's, Like & wildcards, I might use a table of unacceptable strings and loop through a recordset of the user table using Instr function to find the nonconformists. What then? Perhaps disable their account and force creation of a username that conforms. That way, you only 'punish' the nonconformists. You still need a barrier that will not allow this situation to recur.

 

[craving94509]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I don't think that it is entirely clear exactly what the definition of a "proper" name is for you. See if this is on the right track.
If not, then you need to clarify better what constitutes a "proper" name.

22 02 02.xlsm
	A	B
1	User name	Wrong?
2	Admin	x
3	Bob Jones
4	Bob test user	x
5	Bob test	x
6	Bob Test
7	Admin2	x
8	Fred Derf
9	Don ADAMS	x
10	Joseph Jack Smith	x
User Names

Thank you for the advice on updating my account details, that has been done.

Using the term proper name was not the best choice of words. I am not concerned with the letter case like a true proper name would suggest, rather that the name field has 2 components prefereably a first and last. Words like "admin", "test" or numbers are likely canidates for review.

A bit of background. This extract is coming from a hosted platform where company administrators can add users to thier company profile. Since this is a SaaS we do not have control over the formatting of the user name field in the DB, which does not enforce structured data for first and last name as seen below.

Below is an exaple of what we would consider a good user name vs. a bad one.

Appreciate the responses and help.

 

[Micron]

You don't need all those tests. Based on a quick look, if the field contains "admin" and/or does not contain a space it seems you want to flag it as non-comforming. Easy enough to do with Instr function in a query or vba code using Select Case block where you can trap for as many conditions as you can think of.
However, a bit confused still. The db is Access or some other app? You certainly can enforce whatever you want on the Access db?

 

[Peter_SSs]

Thank you for the advice on updating my account details, that has been done.

Thanks for doing that

Next thing to investigate is XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You certainly can enforce whatever you want on the Access db?

.. perhaps not?

we do not have control over the formatting of the user name field in the DB

Would this (tests as suggested by @Micron but with worksheet formulas rather than vba) be any use?
Note that checks of this type can rarely be 100% assured. For example, this person could have a legitimate account in your db but would show as "Bad" with my formula.

craving94509.xlsm
	A	B
1	User name	Good/Bad
2	Admin Bambino	Bad
3	Administration	Bad
4	AFISHER	Bad
5	Alana	Bad
6	A. Scot Black	Good
7	Aaron Binns	Good
8	AARON BURNETT	Good
User Names (2)

Cell Formulas
Range	Formula
B2:B8	B2	=IF(OR(SEARCH("admin",A2&"admin")<LEN(A2),FIND(" ",A2&" ")>LEN(A2)),"Bad","Good")