Split a name filed.
- Thread starter Ganley
- Start date
neileg
AWF VIP
- Local time
- Today, 13:43
- Joined
- Dec 4, 2002
- Messages
- 5,970
Assuming you only have two names and they are separated by a space, you can use InStr() to find the location of the space and Left() and Right() to extract the two names.
Been done before in these forums so you can do a search for more details.
Been done before in these forums so you can do a search for more details.
I took a slightly more longwinded approach having to regularly split down 80,000+ rows of names so I used VBA to do the following:
1) Split() the name field down using "space" as the separator into an array
2) Discard any single characters (optional)
3) If there two remaining elements, put into firstname, surname
4) If the remaning elements are greater than two and less than six then piece the elements together and examine tblfirstname and tblsurname to attempt to find a match on the string combinations and then put into firstname and surname.
ie "Anne Marie Jones" will split down to "anne", "marie", "Jones". I put, "anne Marie" together and search tblFirstname for a match, if I don't find a match I put "marie Jones" together a search tblSurname for a match. You can probably assume correctly how it deals with double barrelled first AND surnames ("Jean Claude" "Van Damme") and triplebarrelled name ("anne marie" "Van Der Beek") combinations.
5) If no match is found, present the user with the two or three possible combinations of valid name and ask for the correct one.
6) The user can then select the combination as a one time only match, or opt to write the firstname and/or surname combination into the relevant table for future use.
There is also an option to set whether the format is fname/sname or sname/fname.
It is fairly sensitive to badly formatted name columns though and if a name field contains more than 6 elements after single characters are discarded then it simply ignores them and moves on. I would post the code here, but I wrote it a couple of years ago and reading back through it, it's not very well written at all, it's survived on the "if it ain't broke" adage. Though once it's be "trained" on a list once, it will happily churn through 17,000 names in a couple of minutes with a few user prompts (there's a always a couple of names that dont fit the model correctly).
1) Split() the name field down using "space" as the separator into an array
2) Discard any single characters (optional)
3) If there two remaining elements, put into firstname, surname
4) If the remaning elements are greater than two and less than six then piece the elements together and examine tblfirstname and tblsurname to attempt to find a match on the string combinations and then put into firstname and surname.
ie "Anne Marie Jones" will split down to "anne", "marie", "Jones". I put, "anne Marie" together and search tblFirstname for a match, if I don't find a match I put "marie Jones" together a search tblSurname for a match. You can probably assume correctly how it deals with double barrelled first AND surnames ("Jean Claude" "Van Damme") and triplebarrelled name ("anne marie" "Van Der Beek") combinations.
5) If no match is found, present the user with the two or three possible combinations of valid name and ask for the correct one.
6) The user can then select the combination as a one time only match, or opt to write the firstname and/or surname combination into the relevant table for future use.
There is also an option to set whether the format is fname/sname or sname/fname.
It is fairly sensitive to badly formatted name columns though and if a name field contains more than 6 elements after single characters are discarded then it simply ignores them and moves on. I would post the code here, but I wrote it a couple of years ago and reading back through it, it's not very well written at all, it's survived on the "if it ain't broke" adage. Though once it's be "trained" on a list once, it will happily churn through 17,000 names in a couple of minutes with a few user prompts (there's a always a couple of names that dont fit the model correctly).
Though as this question comes up quite a lot I'll try and have a look at tidying up the code into something a little more straightforward and workable, although I wouldn't hold your breath on that being completed anytime soon.
