Limitations of text formatting formulas in LOOKUP fields

Lookups are a strange animal…

Try adding &’’ to the end of your references to the lookup field inside the substitute function - this might coerce it to behave as a string.

SUBSTITUTE(“,”, “ & “, {Lookup field name}&’’)

SUBSTITUTE(“,”, “ & “, {Lookup field name}&’’)

Unfortunately, I already tried that from another post I saw and it doesn’t work.

Lookups are returned as arrays – even if they return only a single value, and even if they display as if they were a string. @Jeremy_Oglesby’s suggestion should work. (Make sure you’re using straight, not curly, quotes: SUBSTITUTE(',',' & ',{LookupField}&'').

Edit: Oh, duh:

SUBSTITUTE({LookupField}&'',',',' & ')

@W_Vann_Hall, thank you for the explanation. Since LOOKUPs are returned as arrays, do you by any chance know how you would parse out last names in a scenario where we would have 1 or more full names (LOOKUP returns “John Smith, Jane Smith” and we want to output “John & Jane”).

I’m wondering if we just need to restructure our base to better accommodate one-to-many relationships (multiple full names linked to a single record).

Names are hard. :winking_face: I can provide you a formula to turn ‘John Smith, Jane Smith’ into 'John & Jane’¹ — but it would fail at the first incidence of ‘John Smith, Jane Jones-Smith’. (To say nothing of ‘Jasper Johns, Ben Johnson’ — or, for that matter, ‘W Vann Hall, Annie Hall’.)

In the past, I’ve usually found myself using some sort of hinting to help ensure names are parsed properly. One of the simplest yet most useful is one I stole from PC File back in the days of coal-powered PCs: Entering names as LastName~FirstName, with the tilde character something of a visual mnemonic for ‘swap these around’. For one thing, it made it possible to devote only a single field to the name — at a time when records were severely limited as to number of fields and amount of data — and still have it sort successfully; for another, that simple trick would make the ugly formula that closes this post a lot more bulletproof.

Honestly, if your base requires multiple [Name] records to be linked to a single [Address] (or whatever) record, I’d recommend you either structure [Name] to include separate {FirstNames} and {LastName} fields (with {FirstNames} being essentially everything but the last name — ‘W. Vann’, ‘Mary Kay’, ‘J. R. R.’ — and then perform your look up on a formula field with the formula {LastName}&'~'&{FirstNames} OR that you somehow hint the last name, at least for names that don’t match a standard pattern.

You’d then want to harden the afore-mentioned ugly formula (most notably, by incorporating an explicit comparison between the last names of both parties instead of seeing how many times the first party’s last name can be found in the lookup field). In any case, though, knowing for certain what constituted each parties’ last name would make the routine easier and a lot safer.

And don’t even think of addressing anything to ‘John, Jane, and Little Jimmy’. Given Airtable’s current lack of support for arrays and looping, you’d have to define an explicit formula for up through the maximum number of linked names as you ever expected to encounter. (Or you could do as we do when it comes to addressing Christmas cards, where three of Mom’s siblings have 6, 8, and 10 kids, respectively: We write ‘The Cookes’, and let it go at that.)

I don’t know how helpful it may be as regards your use case, but a while back I put together a demo base illustrating how one can use various forms of hinting to support a number of different naming conventions. Still, you might find something there worth incorporating.

1. I tossed together a formula that, in theory, would convert such a lookup value as ‘John Smith, Jane Smith’ to ‘John & Jane’. However, it hasn’t been tested — for that matter, it hasn’t even been saved as an Airtable formula, so there could easily be syntax errors. I wrote it mainly to illustrate how gnarly a formula it is — especially once you consider how fragile it is, as well. With those caveats, here it is:

IF(
    LEN(
        {Name LU}&''
        )-LEN(
            SUBSTITUTE(
                {Name LU},
                MID(
                    {Name LU}&'',
                    FIND(
                        ' ',
                        {Name LU}&''
                        )+1,
                    FIND(
                        ',',
                        {Name LU}&''
                        )-(FIND(
                            ' ',
                            {Name LU}&''
                            )+1)
                    ),
                ''
                )
            )=LEN(
                MID(
                    {Name LU}&'',
                    FIND(
                        ' ',
                        {Name LU}&''
                        )+1,
                    FIND(
                        ',',
                        {Name LU}&''
                        )-(FIND(
                            ' ',
                            {Name LU}&''
                            )+1)
                    )
                )*2,
    LEFT(
        {Name LU}&'',
        FIND(
            ',',
            {Name LU}&''
            )-1
        )&
      ' & '&
      MID(
        {Name LU}&'',
        FIND(
            ',',
            {Name LU}&''
            )+2,
        FIND(
            ' ',
            {Name LU}&'',
            FIND(
                ',',
                {Name LU}&''
                )+2
            )-FIND(
                ',',
                {Name LU}&''
                )+2
        )
    )

IF(
    LEN(
        {Name LU}&''
        )-LEN(
            SUBSTITUTE(
                {Name LU},
                MID(
                    {Name LU}&'',
                    FIND(
                        ' ',
                        {Name LU}&''
                        )+1,
                    FIND(
                        ',',
                        {Name LU}&''
                        )-(FIND(
                            ' ',
                            {Name LU}&''
                            )+1)
                    ),
                ''
                )
            )=LEN(
                MID(
                    {Name LU}&'',
                    FIND(
                        ' ',
                        {Name LU}&''
                        )+1,
                    FIND(
                        ',',
                        {Name LU}&''
                        )-(FIND(
                            ' ',
                            {Name LU}&''
                            )+1)
                    )
                )*2,
    LEFT(
        {Name LU}&'',
        FIND(
            ',',
            {Name LU}&''
            )-1
        )&
      ' & '&
      MID(
        {Name LU}&'',
        FIND(
            ',',
            {Name LU}&''
            )+2,
        FIND(
            ' ',
            {Name LU}&'',
            FIND(
                ',',
                {Name LU}&''
                )+2
            )-FIND(
                ',',
                {Name LU}&''
                )+2
        )
    )

@W_Vann_Hall, I’m sure you hear this a lot but you’re awesome. Thank you for such a detailed response!

I think restructuring our base the way you laid it out makes the most sense. Hopefully, it’ll make things much easier in the future.