Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formula for replacing characters and changing Uppercase to lowercase

4097 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Sally_Romano
4 - Data Explorer
4 - Data Explorer

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.Airtable

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

4 Replies 4
augmented
10 - Mercury
10 - Mercury

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 :slightly_smiling_face:

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
4 - Data Explorer
4 - Data Explorer

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