Need to separate text into two fields. How?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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!
Photo of Evan

Evan

  • 266 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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],",")))
Photo of Evan

Evan

  • 266 Points 250 badge 2x thumb
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
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Evan

Evan

  • 266 Points 250 badge 2x thumb
Thank you!