Forum Discussion
Solved
MS Excel Custom Cell Formatting with Formula.
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,,,
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.
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.
