Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

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

Topic Labels: Formulas
Solved
487 2
cancel
Showing results for
Did you mean:
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:

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

Accepted Solutions
9 - Sun

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)
``````
2 Replies 2
9 - Sun

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)
``````
8 - Airtable Astronomer

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