Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Reformat a list of names

Reformat a list of names

Reformat a list of names


My data table has a field for names of honorees. In the data source table the names in that field are ...

Smith, John; Smith, Jane; Roberts, Sam; Roberts, Karen

What function do I use so that CR XI will display on my report...

John Smith, Jane Smith, Sam Roberts, Karen Roberts

I'm experimenting with split([field], ";") right now but I'm getting errors say the array must be subscripted. I'll keep working with it but was hoping someone has some experience with this.

RE: Reformat a list of names

So you want to replace the ';' with commas and the commas with spaces (after you switch the first and last names)? If this is so. I think it would be a three part process. First (probably with an array). Go through and put each name (last name, first name) in an array. Then go through the array and and switch the first and last names. Lastly, recombine the names with the comma (this is where the join should work). Unfortunately I do not currently have the time to work up code to do this.

RE: Reformat a list of names

Thanks for your input.

What you said about the 3-step breakdown is helpful.

RE: Reformat a list of names

Unless I have misunderstood what you are trying to achieve here, and assuming the field data is exactly as you have shown, this should work:


Local NumberVar i;
Local StringVar R;

For i:= 1 to Ubound(Split({Table.Field}, '; ')) do

R := R + Split(Split({Table.Field}, '; ')[i], ', ')[2] + ' ' + Split(Split({Table.Field}, '; ')[i], ', ')[1] + '; ';

R := Left(R, Len(R) - 2) 

Hope this helps.


RE: Reformat a list of names

Wow pmax9999, it looks like that is going to work. Thanks!

Now I have to figure out what you did so I can help myself next time. pc2

RE: Reformat a list of names

Thanks smitty691. Glad it helped.

Ubound(Split({Table.Field}, '; ')) determines the number of names in the list - in your example 4.

It then loops through each of those 4 names (as separated by the "; ") selects the first name (ie, the second component of the name - as separated by the ", ") adds a space, then the surname, then a semi-colon and another space.

When it finished looping through initial string the result (in the formula it is contained within the variable "R") is a list of names as required, but has an additional "; " at the end. The final line of the formula removes those last 2 characters.

Hope this helps. I'd suggest you have a play with the Split function. It can be a very useful function.


RE: Reformat a list of names

I forget about using ubound. smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close