rcorbitt Posted November 9, 2007 Share Posted November 9, 2007 I am importing a text file that contains (Name) "Smith, Fred A." or "Doe, John Robert" or "Doe, John Robert Sr." or "Jones, Sam". I want to create a calc that will reformat the data to "Fred A. Smith" or "John Robert Doe" or "John Robery Doe Sr." or "Sam Jones". I tried this: RightWords (Name; 2 ) & " " & LeftWords (Name; 1) It works ok when there is a middle initial, but fails miserably with only a first/last or a first/last/suffix. How do I write a calc that uses the comma as the delmiter for the last name, and spaces for the first and middle and suffix? Thanks in advance. Link to comment Share on other sites More sharing options...
comment Posted November 9, 2007 Share Posted November 9, 2007 It's fairly easy to separate the last name from the rest using the position of the comma as the anchor: last = Left ( Name ; Position ( Name ; "," ; 1 ; 1 ) - 1 ) ; rest = Right ( Name ; Length ( Name ) - Position ( Name ; "," ; 1 ; 1 ) - 1 ) Hovewer, a computer cannot tell by itself if "Sr." is a suffix or an initial. You might want to look at the "Universal Capitalizer" demo here to get some ideas how it can be handled, but it can never be perfect. Link to comment Share on other sites More sharing options...
rcorbitt Posted November 9, 2007 Author Share Posted November 9, 2007 THANKS! This works pretty well but ... The fourth "word" of the field will always be the suffix, if it exists; and I need to move it to the end. (BTW - Universal Capitalizer is incredible, I have it working on the address field already.) Currently "Doe, John Robert Sr." becomes "John Robert Sr. Doe". How do I get the fourth word to the end, only if it exists? "John Robert Doe Sr." Link to comment Share on other sites More sharing options...
comment Posted November 9, 2007 Share Posted November 9, 2007 The fourth "word" of the field will always be the suffix Will it? How about "Jones, Sam Jr." or "Smith-Jones, John Irving Sr."? You could of course make an assumption that if the 'rest' part has three words, the third word is a suffix. But it wouldn't catch those who don't have an initial and do have a suffix, and it would catch - falsely - those with three first names. Link to comment Share on other sites More sharing options...
rcorbitt Posted November 9, 2007 Author Share Posted November 9, 2007 Maybe a better solution would be just first and last name. Suggestions? Link to comment Share on other sites More sharing options...
comment Posted November 9, 2007 Share Posted November 9, 2007 I don't think there's much to add. The only thing I would suggest is to separate the data into individual fields (FirstName, LastName, Initial, Suffix, Salutation, etc.). It's very easy to put data together, not so easy to take it apart. As I mentioned before, you could make the suffix the last word of "rest" when "rest" has more than 2 words, and mirror the first name calculation accordingly. But I believe in the end there's no alternative to a human eyeballing the results and making intelligent decisions. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6140 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now