Text correction function

Hi All

i have a several SKU which is not standard and need to be corrected,
encloses my work file with expected text inside
and i need a formula to convert the text as per my expectations, a single formula which can correct all original text

thank you
Didien

Try, in cell C3:

Formula:

=SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(A3," ","|",1)," ",""),FIND("|",SUBSTITUTE(SUBSTITUTE(A3," ","|",1)," ",""))-1) &

IF(LEN(MID(SUBSTITUTE(SUBSTITUTE(A3," ","|",1)," ",""),FIND("|",SUBSTITUTE(SUBSTITUTE(A3," ","|",1)," ",""))+1,99))=4,"-" &

MID(MID(SUBSTITUTE(SUBSTITUTE(A3," ","|",1)," ",""),FIND("|",SUBSTITUTE(SUBSTITUTE(A3," ","|",1)," ",""))+1,99),2,2),""),"_","-")

and copy down.

Hi TMS

sorry for late comment, thanks, i have tried to but seems error while run your formula

however i found the formulas .. thanks anyway

Good day Did13n...

can you post your solution so others could learn/benefit from it.

thank you.

No Problem

glad to share ..all i did is by added another column as a key ...and use combine substitue, trim and text location

Thanks for the rep .

You have the advantage of me in that you know the dependencies.

However, your formula:

Formula:

=IF(LEFT(C3,2)="FF",SUBSTITUTE(TRIM(LEFT(A3,12)),"_","-")&"-"&IF(LEFT(C3,2)="FF",MID(A3,14,2),""),SUBSTITUTE(TRIM(LEFT(A3,12)),"_","-"))

could be shorter:

Formula:

=SUBSTITUTE(TRIM(LEFT(A3,12)),"_","-")&IF(LEFT(C3,2)="FF","-"&IF(LEFT(C3,2)="FF",MID(A3,14,2),""),"")

My formula does work with your test data and has no dependency on column C ... which perhaps explains why it is longer.

HTML Code: 

See the attached updated example.

It may not work with your live data if the structure is different.

great ... thanks to shorten formulas @TMS, nice logic !

You're welcome.