Search code examples
exceltextcopy-pastetextpad

Copying garbled column text in excel

I have some data coming in column format separated by commas

name1,age1,school1,subjects1,location1,dob1,doj1
name2,age2,school2,subjects2,location2,dob2,doj2
name3,age3,school3,subjects3,location3,dob3,doj3

some are like this separated by white spaces

name4,age4,school4,subjects4,     location4,dob4,    doj4

name5,age5,school5,   subjects5,location5,dob5,doj5

How can i copy paste them correctly in separate columns in excel Sheet. I have tried doing this using textpad , by copying it and then replacing the comma by blank space and then selecting the block select mode and copying each column seperately and then pasting it. But process is too cumbersome and time taking.

Solution

  • It is very easy to do. Select your text and go to Data --> Data Tools --> Text To columns. Then select "Delimited" option and press NEXT. Afterwards, tick "comma" and "space" and press finish. It will do everything in a separate column.