Help

Reformatting a string: how to add hyphen in front of last digit?

Topic Labels: Formulas
Solved
Jump to Solution
663 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

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-[9 digits]-[1 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:
Screen Shot 2022-03-07 at 1.35.19 PM

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

Accepted Solutions
Rupert_Hoffsch1
10 - Mercury
10 - Mercury

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)

See Solution in Thread

2 Replies 2
Rupert_Hoffsch1
10 - Mercury
10 - Mercury

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.