Split a column into two Columns in excel

[waqar1239]

Hello Team,
I have this below data in one cloumn.
Cloumn A
119WATER:FYI0333
120MATTER:BYT0444
144CAUSE:PRI0345

I would like to split this data into the two column
such as
Cloumn A
119WATER
120MATTER
144CAUSE

Column B
FYI0333
BYT0444
PRI0345

Please advise me on this
Thanks in Advance
Cheers
Waqar

 

[Rick Rothstein]

Select Column A and then call up the Text To Columns dialog box (Data tab, Data Tools panel)... on first dialog page, select Delimited and then click Next button...on second dialog page, select the Other checkbox and put a colon ) in the field next to it... click the Finish button.

 

[waqar1239]

Thanks for your support on this matter. Cheers
Waqar

 

[waqar1239]

Sorry to asked you one more then.

Could it possible or you to advise me a formula for this steps. Please

 

[alansidman]

In B1 =LEFT(A1,FIND(":",A1)-1) and copy down
In C1 =RIGHT(A1,LEN(A1)-FIND(":",A1)) and copy down

 

[waqar1239]

Great it works
Heaps of thanks

 

[Rick Rothstein]

Great it works
Heaps of thanks

It does? You specifically said in your first message that you wanted the first part of the text in Column A (replacing what was originally there)... a formula solution cannot do that (short of copy/pasting values over top of the original values and then deleting the duplicate data).