How to trim values in custom format in excel?-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

Perhaps you could try using TEXTSPLIT() in MS365

• Formula used in cell B2

=TEXTJOIN("-",,LEFT(TEXTSPLIT(A2,"-"),12))

In Google Sheets

• Formula used in cell B2

=JOIN("-",INDEX(LEFT(SPLIT(A2,"-"),12)))

CodePudding user response：

I suppose this is a GS question. If so, try to use REGEXREPLACE():

=REGEXREPLACE(A1,"^([^-]{12})[^-]*","$1")

For a range of values:

=INDEX(IF(A1:A="","",REGEXREPLACE(A1:A,"^([^-]{12})[^-]*","$1")))