Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Formulas
Solved
Jump to Solution
2123 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.