Excel Enquiry?

Question

I have a massive data set which i need to statistically analyse. When i copy the raw data into excel it occupies one column even though it appears as three columns inside column A. How do i time efficiently separate these 3 columns into recognisable columns in excel i.e. A,B,C? The data set consists of 1000's of individual datum so it would be a life saver to find out!

Answer 1

Use file > open in excel to open your text file. You need to select the file type of .txt. You will have the options to select delimiters or fixed width. Without seeing your text file, I don't know which option is the best but try them. There is only a few and if it fails, close it without saving and try again until you get it.

Answer 2

ok, here's what I would do..... use control f, and replace the space with a semicolon, then copy all of it into notepad, save it, then open the text document w/ excel, it's going to ask you if you want to open it "delimited" or "fixed width", go w/ delimited, then it's going to ask you to set the delimiters, check the box for semicolon. This will separate the items for you into different columns.

Seriously, it's the quickest way I know to do it.

Answer 3

If Data >Text to columns didn't helped you enough, see this formula...
Assuming that your three in one column is in B starting from B1, and the charactor that is separating each column is the colon :, then in C1, paste this
=LEFT( B1, SEARCH( ":", B1))
to get the first value
in D1, paste this
=MID( B1, SEARCH( ":", B1)+1, SEARCH( ":", B1, SEARCH( ":", B1)-1))
to get second value
and in E1, paste this
=MID(B1;SEARCH(":";B1;SEARCH(":";B1)+1)+1;500)
to get the third value

The 500 in the end assuming thaat the 3rd column is no longer than 500 chars

Enjoy my profile, I am the VBAXLMan

Answer 4

Highlight the area you want to paste it to. Occupy A,B,C and then paste it in

Answer 5

DATA | TEXT TO COLUMNS.