Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 18th 06, 05:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 78
Default 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?


  #2   Report Post  
Old September 18th 06, 05:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,203
Default 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?

  #3   Report Post  
Old September 18th 06, 05:53 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 427
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Unique Values, Then Extract Again to Remove Suffixes Karl Burrows Excel Discussion (Misc queries) 23 June 25th 05 10:37 PM


All times are GMT +1. The time now is 02:45 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017