Discussions

Expand all | Collapse all

Need to separate text into two fields. How?

  • 1.  Need to separate text into two fields. How?

    Posted 02-15-2017 23:41
    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!


  • 2.  RE: Need to separate text into two fields. How?

    Posted 02-15-2017 23:48
    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],",")))


  • 3.  RE: Need to separate text into two fields. How?

    Posted 04-10-2017 22:32
    Thank you for this reply.

    Do you have a way to reverse the process?

    Take a [first name Last Name] and make it [Last Name, First Name]?

    thank you


  • 4.  RE: Need to separate text into two fields. How?

    Top
    Contributor
    Posted 04-11-2017 04:52
    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.


  • 5.  RE: Need to separate text into two fields. How?

    Posted 04-11-2017 17:31
    Thank you!