Copying garbled column text in excel

Question

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.

i want that data to be copied in the excel without whitespaces and commas.I mean first element in first columns, second in second and so on... — Raulp, Jan 29 '16 at 06:29

score 2 · Accepted Answer · answered Jan 29 '16 at 06:58

2

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.

answered Jan 29 '16 at 06:58

Tango_Mike

104
11

score 1 · Answer 2 · answered Feb 01 '16 at 19:40

1

If there are no commas in your data, which would likely be qualified with double quotes, do a Regex Replace.

Find: +,

Replace ,

Works in TextPad and most any other text editor that can do Regex.

answered Feb 01 '16 at 19:40

Kennah

487
1
5
16

Copying garbled column text in excel

2 Answers2