I have a column of cells containing content in the format ABCDP2S6_EFGHP2Te6_X_YY.Y_ZZZ
I want to use a formula command to output CDABP2S6-GHEFP2Te6
I googled and it seems to involve REPLACE and FIND, but can’t figure out a right way to do it.
Any help?
>Solution :
You could try, assuming as per your comments, the same length:
Formula in B1:
=CONCAT(MID("-"&A1,{4,2,6,1,13,11,15},{2,2,4,1,2,2,4}))
Here I simply use an array of starting indexes to grab substring of length 2, 4 or 5 characters. To avoid a somewhat lengthy formula and double use of MID() the first step was to add an hyphen to the input which we can reference in the indexes too. If this does not paint a clear picture, just skip that part and use original input like so:
=CONCAT(MID(A1,{3,1,5},{2,2,4}),"-",MID(A1,{12,10,14},{2,2,5}))