IMPLEMENT: In Column A We Just Need To Paste The Space Separated Text And Apply The Formula In Column B – C – D – E – F As Mentioned Below. Once The Formula Is Applied You It Will Automatically Separate Text To Columns. Please Check Formula & Notes & Image Below.
NOTE: Please Change The Reference If You Want To Apply On Different Range. Also If you Have Comma Separated Text You Can Change Replace The Space With Comma And It Will Work For You. You Can Replace Any Character.
NOTE: This Formula Works For 5 Space Separated Text Only. But By Editing The Formula It Is Possible To Increase For More Text.
COLUMN B Formula : =IF(LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))=0,A2,IFERROR(LEFT(A2,FIND(” “,A2,1)-1),””))
COLUMN C Formula : =IF(LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))>=2,IFERROR(MID(A2,FIND(” “,A2,1)+1,(FIND(” “,A2,FIND(” “,A2,1)+1)-FIND(” “,A2,1)-1)),””),IF(LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))>=1,MID(A2,FIND(” “,A2,1)+1,LEN(A2)-FIND(” “,A2,1)+1-1),””))
COLUMN D Formula : =IFERROR(IF(LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))>=3,MID(A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))-1-FIND(” “,A2,FIND(” “,A2,1)+1)),MID(A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1,LEN(A2)-FIND(” “,A2,1)+1-1)),””)
COLUMN E Formula : =IFERROR(IF(LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))>=4,MID(A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))+1,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))+1)-FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))-1),MID(A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))+1,LEN(A2)-FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))+1)),””)
COLUMN F Formula : =IFERROR(MID(A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))+1)+1,LEN(A2)-FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,FIND(” “,A2,1)+1))+1))+1)+1),””)
TO GET ALL SEPARATED TEXT IN SINGLE CELL FORMULA Download The File Below And Check At Last.
