PDA

View Full Version : split text in different cells after each underscote "_"



amartakale
08-14-2019, 05:52 AM
24812

Tom Jones
08-14-2019, 08:06 AM
Use Text to Columns and use as separator "_".

amartakale
08-14-2019, 07:50 PM
Sir ,but I want formula only.I have lot of data.so I want only formula not by manual

Fluff
08-15-2019, 07:03 AM
In B3 copied right
=TRIM(MID(SUBSTITUTE($A3,"_",REPT(" ",500)),(COLUMN(A1)-1)*500+1,500))

amartakale
08-15-2019, 09:51 PM
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

Tom Jones
08-16-2019, 12:50 AM
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

Fluff
08-16-2019, 05:07 AM
If it's only going to be pdf files, try
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A3,".pdf",""),"_",REPT(" ",500)),(COLUMN(E1)-1)*500+1,500))

Fluff
08-16-2019, 05:12 AM
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))

amartakale
08-16-2019, 05:41 AM
Great,Mind blowing Sir.:clap::clap::clap::yes:yes

Now perfect working.Thanks for your efforts:bow::bow:

Fluff
08-16-2019, 05:56 AM
You're welcome & thanks for the feedback