Help with "IF" statement

[Colin_McDonald]

Hi Folks, I just heard of Airtable today, and I’m looking to see if it would be easier to use for me for an Access Family History project for personal use.

I want to create a Formula field that simplifies surnames beginning with “Mac” & “Mc” to create a “Sort Surname” field where all “Mac” surnames become “Mc” surnames. Then they will sort together, since the two versions are often mis-spelled even for the same person.

I have created a formula using my Surname field as follows (I used 300 for the mid command as I do not know the length of the surname field. Putting it this high should avoid errors)

IF(LEFT(Surname,3,“Mac”),“Mc”& MID(Surname,4,300),Surname)

It works perfectly for MacGillivray, Mackintosh, Macdonald etc, but it is not working for every non “Mac” surname. For example, it is turning Lumsden into Mcsden.

What am I doing wrong so that the Logical test is not working. I had it working perfectly in Access.

Thanks

[Justin_Barrett · Accepted solution]

Welcome to the community, @Colin_McDonald! :grinning_face_with_big_eyes: Your format for the LEFT() function is incorrect. You’re using it under the assumption that it compares the left three characters of {Surname} against the literal string “Mac”, which is not the case. LEFT() does not compare, it only extracts, and only accepts two arguments.

So why is it “working” even though LEFT() is malformed? The first part of the IF() function typically requires a comparison, but will also interpret whatever data it’s fed as some equivalent to either TRUE or FALSE. In this case, it’s being fed a string from LEFT(). An empty string equates to FALSE; any other string equates to TRUE, which is why every surname is being modified, not just those starting with “Mac”.

The correct syntax for comparing those left three characters against the literal string “Mac” would be:

LEFT(Surname, 3) = "Mac"

That would return TRUE or FALSE to the function, which turns your full formula into this:

IF(LEFT(Surname, 3) = "Mac", "Mc" & MID(Surname, 4, 300), Surname)

Here’s the full reference for Airtable’s formula functions:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

[Colin_McDonald]

Hi Justin, thanks for welcome and help. Much appreciated (and very fast).

So far I like the look of Airtable, although I will only be using it for personal projects.

Many thanks

Colin

[Colin_McDonald]

Meant to add that the explanation was very helpful, and the solution worked perfectly.

Thanks

[Justin_Barrett]

Colin_McDonald:

I will only be using it for personal projects.

Famous last words (speaking from experience). :winking_face: I thought that I might do a handful of personal things in Airtable when I started back in February. A short while later I found myself wrangling several dozen bases, including rolling my own accounting system and ditching a service I’d been using for many years. The list keeps growing.