Formula for replacing characters and changing Uppercase to lowercase

[Sally_Romano]

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:

[augmented]

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.

[Sally_Romano]

augmented:

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

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:

[augmented]

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!

[Sally_Romano]

augmented:

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

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