Splitting Names in Excel - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Splitting Names in Excel

Windle Day

4 years ago

I have inherited an Excel "database" with over 50,000 names stored as first name, middle initial, or middle name spelled out, last names, and hyphenated names (sometimes not hyphenated)...and you guessed it...all in one field. To make things worse, many records are duplicated with a new variation in spelling. There is no rhyme or reason to this database. How would you approach deconstructing this concatenated monstrosity???

	Scott Axton @Reply 4 years ago
First and most important: Work with a copy of your Excel. Back up! This will give you a start: Text to Columns Access Expert Level 25 covers the String Functions you will need to complete your project. Unfortunately you will probably only get 90 - 95% there. The rest will require some manual intervention.

	Windle Day OP @Reply 4 years ago
Thank you sir...this should be fun :-)

	Scott Axton @Reply 4 years ago
Your welcome. The key is to look for patterns that you can work with. You'll have to run through multiple times cleaning as you go. Similar to the examples given in the Replace Function video. Another good resource.

	Richard Rost @Reply 4 years ago
What Scott said. :)

	Scott Axton @Reply 4 years ago
Grrr - Spelling. You're not your. Hey Richard - I'd really LOVE an edit button. :)

	Windle Day OP @Reply 4 years ago
Hahaha...gotta love developer OCD :-)

	Richard Rost @Reply 4 years ago
Workin' on it, Scott.

	Scott Axton @Reply 4 years ago
I know Richard - giving you grief. LOL

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View