Jump to content

Reformat name field


This topic is 6067 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

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

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

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

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

This topic is 6067 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.