SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
samyonez
Topic Author
Posts: 219
Joined: October 7th, 2004, 10:01 am

pasting notepad data into excel

August 20th, 2007, 11:39 am

when i paste whitespace-delimited data into excel from notepad, what governs whether each row of data goes into a single string in one cell versus being spread across multiple columns? it seems that sometimes it does one, sometimes the other, somehow depending on things that i have done recently but i'm not sure what. infuritiatingly, it seems to remain stuck at whichever way i don't want it to be.isn't there a way to contol it from vba, e.g. something like a "Application.PasteDataSetting = xlSingleColumn"??
 
User avatar
cemil
Posts: 221
Joined: September 16th, 2005, 7:44 am

pasting notepad data into excel

August 20th, 2007, 12:13 pm

you need to use "DATA/TEXT TO COLUMNS" to convert the data.
 
User avatar
samyonez
Topic Author
Posts: 219
Joined: October 7th, 2004, 10:01 am

pasting notepad data into excel

August 20th, 2007, 12:42 pm

i want the data to paste into one column, so that i can remove unnecessary whitespace myself (e.g. i need to change "17 Aug 07" to "17Aug07" to stop it splitting out the date into 3 cells) before using the text to columns. problem is, as soon as i do this once, it somehow remembers it & the next paste is spread across columns, chopped-up dates & all, & i can't seem to set it back.
 
User avatar
Wibble
Posts: 426
Joined: January 23rd, 2004, 3:15 pm

pasting notepad data into excel

August 20th, 2007, 1:27 pm

can't you use another delimiter between dates like comma?
 
User avatar
samyonez
Topic Author
Posts: 219
Joined: October 7th, 2004, 10:01 am

pasting notepad data into excel

August 20th, 2007, 2:54 pm

format of the data is not my choice...i just have a task where i have to repeatedly copy & paste sections of data from given txt files, but it pastes in wrong & is a pain in the ass & not worth it to write a lot of code or formulas to parse the data.
 
User avatar
INFIDEL
Posts: 749
Joined: November 29th, 2005, 4:17 pm

pasting notepad data into excel

August 20th, 2007, 4:34 pm

cemil is right. When pasting (in MS Excel) a "paste options" tab appears. Your options are Match Destination Formatting and Use Text Import Wizard. Choose the latter. A window appears, click the "fixed width" radio button; in the next window, the data is automatically in the correct columns, just click next. A pain in the bum. Edit: Just read your next post! Forget the above. Before pasting, set the cell type to "Text". Then 17 Aug 07 will paste into one column. Excel parses what you try to paste in according to the cell type.
Last edited by INFIDEL on August 19th, 2007, 10:00 pm, edited 1 time in total.
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On