ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Extracting names and suffixes (https://www.excelbanter.com/excel-discussion-misc-queries/110418-extracting-names-suffixes.html)

Rookie_User

Extracting names and suffixes
 
I need to extract fname, lname, middle initial and suffix along with license.
The problem is the data is not all consistent - imgaine that. The data will
sometimes look like this:

Jim Case DMD
Claude G. Rick DDS
NILE P ERSLAND DDS PC
Warren Urda, D.D.S.
Don E. Burk DMD
Dr. Nicholas Feldman
MARK A JUST DDS LLC
J. Libby DDS

Any creative idea's please?


JLatham

Extracting names and suffixes
 
You might look at this entry from Anthony D where he was helping with a
similar issue (splitting names - but not quite as complex)
http://www.microsoft.com/office/comm...a-c96f22da49f7

identify the space as the 'delimiter' and see where to go from there. At
least you'll probably have the information broken into individual cells,
although you are obviously going to have to do some manual moves after that.
Even a VBA solution would be difficult because of all the vagueness involved,
as
Name may or may not have middle initial,
may have Dr. at start vs DDS at end
Variety of license identifiers involved.


"Rookie_User" wrote:

I need to extract fname, lname, middle initial and suffix along with license.
The problem is the data is not all consistent - imgaine that. The data will
sometimes look like this:

Jim Case DMD
Claude G. Rick DDS
NILE P ERSLAND DDS PC
Warren Urda, D.D.S.
Don E. Burk DMD
Dr. Nicholas Feldman
MARK A JUST DDS LLC
J. Libby DDS

Any creative idea's please?


Dave O

Extracting names and suffixes
 
The many variations, unfortunately, will require manual processing on
your part. The best way to start that I can think of is to use the
Text To Columns feature and parse the data using the blank space as
delimiter. That will get individual words (Title, Fname, Lname,
degree, etc) into individual columns.

Then, perform a sort of all rows, sorting on column D. This will get
all the FName LName Degree people into one area. Segregate that data,
then sort the remaining rows on Col E, then F etc., handling the
information in groups step by step.



All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com