How to trim values in custom format in excel?

I have a column with values like ‘ABCRSETCMS4008-M05’. Notice before the first ‘-‘ if there are more than 12 characters, I want to trim it to 12 characters. So my trimmed value should be ‘ABCRSETCMS40-M05’. How can I do this in Excel? What should be my formula for this?

Also, there are values which have less than 12 characters. I don’t want to affect those.

>Solution :

For Excel try-

=LEFT(TEXTBEFORE(A1,"-"),12)&"-"&TEXTAFTER(A1,"-")

For google-sheet try-

=LEFT(INDEX(SPLIT(A1,"-"),1,1),12)&"-"&INDEX(SPLIT(A1,"-"),1,2)

enter image description here

Leave a Reply