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

2015-05-21 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 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, 2015

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(SUBSTITUTE(A1351,-,),