Skip to main content

Formula for replacing characters and changing Uppercase to lowercase

S

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! 🙂

4 replies

A
  • augmented
  • Inspiring
  • 326 replies
  • March 3, 2021

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.

S
  • Sally_Romano
  • Author
  • New Participant
  • 2 replies
  • March 3, 2021
augmented wrote:

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 🙂

A
  • augmented
  • Inspiring
  • 326 replies
  • March 3, 2021
Sally_Romano wrote:

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!

S
  • Sally_Romano
  • Author
  • New Participant
  • 2 replies
  • March 3, 2021

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

Reply


Most helpful members this week

Terms & Conditions