Skip to main content

In the primary field, I’m trying to create an ID Code for my ancestors and I need some help.



An example ID Code is:


First three letters of surname, first three letters of name, year of birth


eg Denis O’Brien born in 1836 = ObrDen1836



However, I have two issues with this example as the formula has created O’BDen1836



How do I remove the apostrophy in the surname and change the capital B to a lower case ‘b’ to change O’BDen1836 to ObrDen1836?



Is this even possible?



My current formula is: LEFT({Surnames}, 3)& LEFT({Name}, 3)& YOB



I’ve attached a screenshot for reference also.



Thank you so much, your help is very much appreciated - and may well save my sanity! 🙂

Hi Sally. I’m sure there’s a better way, but here is a solution to the first part of your formula.



LEFT(SUBSTITUTE({last name},"’",’’),1)&RIGHT(LEFT(LOWER(SUBSTITUTE({last name},"’",’’)),3),2)



You would then add the “&LEFT({Name},3)&YOB”. My suggestion relies on the surname being capitalized. If you had a name like “de Relion”, then you would be presented with two new problems unfortunately.



Good luck.

Hi Sally. I’m sure there’s a better way, but here is a solution to the first part of your formula.



LEFT(SUBSTITUTE({last name},"’",’’),1)&RIGHT(LEFT(LOWER(SUBSTITUTE({last name},"’",’’)),3),2)



You would then add the “&LEFT({Name},3)&YOB”. My suggestion relies on the surname being capitalized. If you had a name like “de Relion”, then you would be presented with two new problems unfortunately.



Good luck.




Thank you so much for this help.



I’ve changed all the surnames to uppercase (which is standard in genealogy so that was a great tip) and I’ve copied and pasted the formula, but it’s not working.



Here it is: NB changed ‘last name’ to Surname to fit with my system LEFT(SUBSTITUTE({Surname},"’",’’),1)&RIGHT(LEFT(LOWER(SUBSTITUTE({Surname},"’",’’)),3),2)& LEFT({Name}, 3)& YOB



Can you see where I’m going wrong?



Sall 🙂



Thank you so much for this help.



I’ve changed all the surnames to uppercase (which is standard in genealogy so that was a great tip) and I’ve copied and pasted the formula, but it’s not working.



Here it is: NB changed ‘last name’ to Surname to fit with my system LEFT(SUBSTITUTE({Surname},"’",’’),1)&RIGHT(LEFT(LOWER(SUBSTITUTE({Surname},"’",’’)),3),2)& LEFT({Name}, 3)& YOB



Can you see where I’m going wrong?



Sall 🙂


I’m pretty sure the answer lies in the quotes throughout the formula. One thing you can rarely do safely is copy/paste a formula with quotes into Airtable. I would suggest that you go replace all of the single and double quotes manually. In the SUBS function, the first quote group is a double quote around a single quote ("’"). The second is just two consecutive single quotes (’’). Let me know if that works.



Good luck!



OMG that’s perfect! Thank you so so much Augmented. Have a wonderful day 🙏 :grinning_face_with_big_eyes:

Reply


Terms & Conditions