Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 02, 2021 05:40 PM
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! :slightly_smiling_face:
Mar 02, 2021 06:35 PM
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.
Mar 02, 2021 06:54 PM
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 :slightly_smiling_face:
Mar 03, 2021 06:00 AM
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!
Mar 03, 2021 02:14 PM
OMG that’s perfect! Thank you so so much Augmented. Have a wonderful day :pray: :grinning_face_with_big_eyes: