Skip to main content

I am trying to solve the following problem.



I have thousands of registration numbers that always start with a “T” and are followed by 10 digits.


This is how they are delivered to me T3090728379.



But they all need to look like this T-309072837-9


which translates to the following format T-g9 digits]-t1 digit]


The last digit can be anything between 0-9.



What formula could I use to reformat these fields in Airtable?


I managed to substitute “T” with “T-” but I do not know how to get a hyphen in front of the last digit.


Is this something one would use the LEN() function for? (I have never been able to fully understand how this works…)



I’d appreciate any help. Thank you so much in advance.



This is how far I got:




IF(STRING,SUBSTITUTE(STRING,"T","T-"))

Hi Markus! If the string is always of same length, then this formula does the trick:



LEFT(String, 1)&"-"&MID(String, 2, 9)&"-"&RIGHT(String, 1)

Hi Markus! If the string is always of same length, then this formula does the trick:



LEFT(String, 1)&"-"&MID(String, 2, 9)&"-"&RIGHT(String, 1)



Thank you so much! Yes, this makes total sense.

Reply


Terms & Conditions