• Registered: 2009-04-22
  • Posts: 1

Topic: I want an empty column to split the contents that I will

I want an empty    column to split the contents that I will eventually enter into three columns but can't set it to "text to columns" as there is no data entered yet.  How can I formulate empty columns to split the contents of the future cell contents. 

Quote Post 1

  • Registered: 2009-04-17
  • Posts: 440

Re: I want an empty column to split the contents that I will

Hi Janna,

If you want to split data using text to column option then you have to enter data first.
But you can use this formula for splitting data. The data will be starting from cell A2. The results will be in B2, C2 and D2.
1.If the data is seperated by a comma, type this formula in the formula bar for B2.
=LEFT(A2,IF(ISERROR(FIND(",",A2,1)),LEN(A2),FIND(",",A2,1)-1))
2.Type this formula in the formula bar for C2.
=TRIM(IF(ISERROR(FIND(",",A2,1)),A2,MID(A2,FIND(",",A2,1)+1,
IF(ISERROR(FIND(" ",A2,FIND(",",A2,1)+2)),LEN(A2),
FIND(" ",A2,FIND(",",A2,1)+2))-FIND(",",A2,1))))
3.Type this formula in the formula bar for D2.
=TRIM(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,
FIND(" ",A2,FIND(",",A2,1)+2))),LEN(A2),
FIND(" ",A2,FIND(" ",A2,FIND(",",A2,1)+2))-1)))
Make sure that the data is seperated by a comma.

If this process takes too much time then i think you have to use visual basic codes.




Quote Post 2