First Name Last Name to Last Name, First Name 1

[mwake]

I am using CR11 with a SQL Server db as my data source and I have a name field that has first name and last name. I want to convert the first name last name to last name, first name. Example: John Smith needs to Smith, John. How do I do it??

 

[kray4660]

This should work (the calculations might be off by one)

numbervar delim := InStrRev({Name Field}, " ")
right({Name Field}, len({Name Field})-delim)+", "+left({Name Field}, delim-1)

 

[pmax9999]

Or alternatively and perhaps simpler:

Split({Table.Field}, ' ')[2] + ', ' + Split({Table.Field}, ' ')[1]

The success of any approach will depend on the consistency of data. My approach will require that the data consists of 2 words (names) separated by a single space as per the example provided. You may want to add If-Then-Else tests to deal with variations in the data, but you will need to understand all of the possible variations in the way the data has been entered so you can anticipate the issues and incorporate the appropriate code to deal with them.

Hope this helps.

Cheers
Pete

 

[mwake]

Thanks Pete. Your formula did the trick!!