Forum Discussion

AmberPollard's avatar
AmberPollard
Qrew Assistant Captain
9 years ago

How to List First and Last Initials from List User Field?

I've converted my list-user field to text using the UserListToNames function.  Now, I want to pull out the first and last initial for each user.  So when I get the results  "John Smith; Bob Jones; Robert Green" I want to convert that to "JS, BJ, RG".  How can I do this?

Thank you!
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    Hi Amber,

    This is fairly complex! It was fun though. Try this:

    List(", ",

    Left(NotLeft(Trim(Part(ToText(UserListToNames([User List],"LF")),1,";"))," ,"),1)&
    Left(Trim(Part(ToText(UserListToNames([User List],"LF")),1,";")),1),

    Left(NotLeft(Trim(Part(ToText(UserListToNames([User List],"LF")),2,";"))," ,"),1)&
    Left(Trim(Part(ToText(UserListToNames([User List],"LF")),2,";")),1),

    Left(NotLeft(Trim(Part(ToText(UserListToNames([User List],"LF")),3,";"))," ,"),1)&
    Left(Trim(Part(ToText(UserListToNames([User List],"LF")),3,";")),1)

    )

    What you will want to do is consider how many possibilities you might have in your user list field. I have solved for 3 above. To add more possibilities, you would add another row like this:

    Left(NotLeft(Trim(Part(ToText(UserListToNames([User List],"LF")),4,";"))," ,"),1)&
    Left(Trim(Part(ToText(UserListToNames([User List],"LF")),4,";")),1)

    With each row you add, you need to increase the first number by 1. See the bolded 4's above. Also note, each row would require a comma after it except for the very last one. (See the first two rows end with: )),1), and the last row ends with )),1)

    Let me know if that works for you. Good luck!