Re-arrange order of string - is this possible?

Question

I am not sure if this is even possible -
I have a table that has 3 name fields - {Name 1}, {Name 2} and {Name 3}. A formula field {All Names} combines the content of all 3 name fields separated by commas.

I am using this formula:
IF({Name 3}, ({Name 1} & ', ’ & {Name 2} & ', ’ & {Name 3}),
IF({Name 2}, ({Name 1} & ', ’ & {Name 2}),
IF({Name 1},{Name 1}
)))
Is there a way to have the name “ENDING” always be at the end of the string, regardless of the name field it was entered in?
So in my example, the second row would read: “Michael, ENDING”.
If this can’t be done in one formula, maybe a second formula field can re-arrange the order. The name “ENDING” will be consistent across the table.
I can of course make sure that “ENDING” is always the last name to appear in any of the 3 name fields. What I’d like to do is make sure that it is indeed always the final entry in the combined string, regardless of what users enter where.
Thank you so much for pointing me in the right direction.

kuovonne · Accepted Answer

I am realizing now, this is too complicated. Better make sure everybody enters the information that should appear last in the correct field…

But thanks for responding, @Raminder_Singh

It is possible with a formula field. The difficulty is not in the formula itself. The difficulty is in seeing the patterns to come up with the formula.

Note that I am assuming that “ENDING” will appear at most one time in the list.

IF(
  FIND("ENDING, ", {All Names}),
  SUBSTITUTE({All Names}, "ENDING, ", "") & ", ENDING",
  {All Names}
)

Notice that if the word “ENDING” is the last word in the list, it will never be followed by a comma. However, if it is not the last word in the list, it will always be followed by a comma and a space. So the algorithm becomes this:

Check to for the string "ENDING, " with the comma and the space.
If you find "ENDING, ", remove it including the comma and space, and tack ", ENDING" on to the end of the result.
If you do not find "ENDING, ", simply use the original string (which will either have ENDING at the end or not have ENDING at all.

By the way, your screen shot looks like you are used to working with Spreadsheets. Because Airtable is a database, not a spreadsheet, I recommend considering a two table system, one for groups, and another table for people, with linked records between the two to show who is in which group.

Of course, you may have other reasons for your base design, such as a need to be able to manually copy/paste from a spreadsheet.

Markus_Wernig · Answer

It is possible with a formula field. The difficulty is not in the formula itself. The difficulty is in seeing the patterns to come up with the formula.

Note that I am assuming that “ENDING” will appear at most one time in the list.

IF(
  FIND("ENDING, ", {All Names}),
  SUBSTITUTE({All Names}, "ENDING, ", "") & ", ENDING",
  {All Names}
)

Notice that if the word “ENDING” is the last word in the list, it will never be followed by a comma. However, if it is not the last word in the list, it will always be followed by a comma and a space. So the algorithm becomes this:

Check to for the string "ENDING, " with the comma and the space.
If you find "ENDING, ", remove it including the comma and space, and tack ", ENDING" on to the end of the result.
If you do not find "ENDING, ", simply use the original string (which will either have ENDING at the end or not have ENDING at all.

By the way, your screen shot looks like you are used to working with Spreadsheets. Because Airtable is a database, not a spreadsheet, I recommend considering a two table system, one for groups, and another table for people, with linked records between the two to show who is in which group.

Of course, you may have other reasons for your base design, such as a need to be able to manually copy/paste from a spreadsheet.

Brilliant! And not too difficult. Thank you so much, @kuovonne! And thank you for not only sending me a solution but also explaining the individual steps to me. This is incredibly helpful to people who don’t work with formulas every day.

My example is not reflective of the actual design of my base. I just set it up as a simplified version to find a solution to my problem. The original base is a true database that keeps track of large amounts of audio metadata for thousands of music tracks. We do take advantage of linked tables and many other advantages a database brings to the table.

The reason for my formula need came up because sometimes we have to arrange and display certain information in a very specific way depending on who it will be sent to eventually.

But you’re right, spreadsheets are part of our workflow. We ingest data that way (thank you Airtable for the CSV Import App!!!) and we have to export CSV files to send data to our clients and to embed it into our audio files.

Honestly, I don’t know what I’d do without Airtable (and without helpful people like yourself).
So, thank you again!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded