MS Excel Custom Cell Formatting with Formula.

[KAM_Mumin]

Hi Everyone!

I make an excel sheet for creating Product ID and there I use formula to create the product ID. But the main thing is need to do that is the cell value format by the custom cell formatting. I want to make the product ID like an example: SA-0001-000001, TI-0002-000002, FA-0003-000003 and this will be a value with counting the zeros because after I need the values. But when i make this with the formula there the values are showing SA-1-1, TI-2-2, FA-3-3 . I attach an screen short for understanding.

Please any of you have any solution then please reply,,,

[#2 · Accepted solution]

@KAM_Mumin 

Presuming data in A1, you could use:

=LET(str,TEXTSPLIT(A1,"-"),prefix,TAKE(str,,1),n,TEXT(CHOOSECOLS(str,2),"0000"),nn,TEXT(TAKE(str,,-1),"000000"),TEXTJOIN("-",,prefix,n,nn))

Flash Fill has mixed results with these IDs.