Attachment 24812
Printable View
Use Text to Columns and use as separator "_".
Sir ,but I want formula only.I have lot of data.so I want only formula not by manual
In B3 copied right
=TRIM(MID(SUBSTITUTE($A3,"_",REPT(" ",500)),(COLUMN(A1)-1)*500+1,500))
Dear Sir,
Great:clap::clap::clap:
Thanks very much.In formula what is meaning of 500.I formula F3 cell , I got answer "13 Aug 2019.pdf" after using your formula,can we remove .pdf directly through this one formula.
Currently I use helper column & remove .pdf & use formula: cell G3 =IFERROR(LEFT(F3,FIND(".",F3)-1)," "). I just say can we will do this in one formula.if not no issue,I just asked.
Again Thanks very much sir:yes
Regards
Amar
Use this formula:
=TRIM(SUBSTITUTE(MID(SUBSTITUTE($A6," - ",REPT(" ";,LEN($A6))),(COLUMNS($B:B)-1)*LEN($A6)+1,LEN($A6)),".pdf",""))
assuming your data start in A6
If it's only going to be pdf files, try
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A3,".pdf",""),"_",REPT(" ",500)),(COLUMN(E1)-1)*500+1,500))
Another option, if there is only ever a . before the extension
=TRIM(MID(SUBSTITUTE(REPLACE($A3,FIND(".",$A3),5,""),"_",REPT(" ",500)),(COLUMN(A1)-1)*500+1,500))
Great,Mind blowing Sir.:clap::clap::clap::yes:yes
Now perfect working.Thanks for your efforts:bow::bow:
You're welcome & thanks for the feedback