Re: $$Excel-Macros$$ Query on Extract word

2015-05-20 Thread Pankaj Sharma
little mistake,, but now done =IFERROR(TRIM(CONCATENATE("MOC ",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1351,".",""),"-","")," ",""),(FIND("MOC",UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1351,".",""),"-","")," ","")))+3),2+(IF(AND((CODE(RIGHT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1351,".",""),"-",""),

RE: $$Excel-Macros$$ Query on Extract word

2015-05-20 Thread Pankaj Sharma
Dear Kalyan, It’s Done!!! PFA =IFERROR(TRIM(CONCATENATE("MOC ",MID(SUBSTITUTE(SUBSTITUTE(A1351,"-","")," ",""),(FIND("MOC",UPPER(SUBSTITUTE(SUBSTITUTE(A1351,"-","")," ","")))+3),2+(IF(AND((CODE(RIGHT(MID(SUBSTITUTE(SUBSTITUTE(A1351,"-","")," ",""),(FIND("MOC",UPPER(SUBSTITUTE(SUBSTITUT

Re: $$Excel-Macros$$ Query on Extract word

2015-05-20 Thread Prashant Pednekar
This will work =IFERROR(IFERROR(MID(A2,FIND("MOC",A2,8),7),UPPER(MID(A2,FIND("moc",A2,7),8))),UPPER(MID(A2,FIND("Moc",A2,7),8))) However may of the cases it is written like MOC 9 or MOC - 08 hence some garbage is appearing. you can remove by text to coloumn command. Prashant On Wed, May 20,