Consulting

Results 1 to 10 of 10

Thread: split text in different cells after each underscote "_"

  1. #1

    split text in different cells after each underscote "_"


  2. #2
    Use Text to Columns and use as separator "_".

  3. #3
    Sir ,but I want formula only.I have lot of data.so I want only formula not by manual

  4. #4
    In B3 copied right
    =TRIM(MID(SUBSTITUTE($A3,"_",REPT(" ",500)),(COLUMN(A1)-1)*500+1,500))

  5. #5
    Dear Sir,

    Great

    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

    Regards
    Amar

  6. #6
    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

  7. #7
    If it's only going to be pdf files, try
    =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A3,".pdf",""),"_",REPT(" ",500)),(COLUMN(E1)-1)*500+1,500))

  8. #8
    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))

  9. #9
    Great,Mind blowing Sir.

    Now perfect working.Thanks for your efforts

  10. #10
    You're welcome & thanks for the feedback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •