Forum Discussion

EvanBohan's avatar
EvanBohan
Qrew Cadet
9 years ago

Need to separate text into two fields. How?

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!

4 Replies

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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],",")))
    • EvanBohan's avatar
      EvanBohan
      Qrew Cadet
      9 years ago
      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
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    9 years ago
    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.