You don't have to turn the comma-delimited string into an array in the first place if the names are already in a string. You just need to trim each name.
MYstringlist = " Mikes , Jack , Nicks , Sally "
Set regex = New RegExp
regex.Global = True
regex.Pattern = "\s*,\s*"
MYstringlist = replace(trim(MYstringlist), "'", "''") 'escape single quotes in names to avoid error in sql
MYstringlist = regex.replace(MYstringlist, "','")
strSql = "SELECT * FROM PeopleDatabase WHERE namesField IN ('" & MYstringlist & "')"