Skip to main content
Question

Need to separate text into two fields. How?

  • February 16, 2017
  • 0 replies
  • 141 views
I have two tables that I need to relate. I figure I can do this by displaying the tables side by side (or in excel) alphabetically.

Problem:
One table has the name field listed as "Last Name, First name".
The other table is "First Name Last Name" (no commas).

I figure I can make them the same if I can separate one of those fields and put it back together in the same format as the other. 

Say I separate the "Last Name, First Name" into "Last Name" and "First Name". Then I can put them together as "First Name Last Name"

Thank you for your help!
      This topic has been closed for replies.

      not tested but try this formula to calculate the result in the format John Smith.

      List(" ",
      Trim(right([last name. first name field],",")),
      Trim(left([last name. first name field],",")))
      • MCFNeil
      • Registered
      • April 11, 2017
      Evan,

      You will want to use a combination of "Left" and "NotLeft" in a formula to separate out the first from the last.  It will look something like this:

      NotLeft([Full Name], " ")&", "&Left([Full Name], " ")

      Let me know if that works for you.
      Terms & ConditionsAccessibility statement