How to Import a Text File into Excel and Change Rows to Columns

No video

How to Import a Text File into Excel and Change Rows to Columns

Рет қаралды 341,943

15 жыл бұрын

This lesson is in response to a viewer who asked me, "How do I change data that runs across a row into data that runs down a columns? I get my original data from a main frame computer - but it takes me too much time to get the data to look the way that I want it to look."
In this video I, first, show you how to get external data into Excel. Next, I show you how to use the "Transpose" feature to change the orientation of your data - make a copy of the original data set with data organized in columns rather than in rows.
Finally, I demonstrate how to use the "text-to-columns" feature in Excel. This allows you to quickly break apart an "address block" - (City, State, Zip Code) that is in one cell into three distict columns. This is a great Time Saver!
I encourage you to send me your special requests. I will post a video lesson to solve your problems as soon as my schedule permits.
Danny Rocks
www.thecompanyrocks.com/excels

Пікірлер: 107

@R00k81 10 жыл бұрын

Consider yourself liked. You just saved me sooooooooooo much time on my project. THANK YOU!!!

@matt1989tomlinson 11 жыл бұрын

You just saved my stats. I had 1000*1000 figures stuck in a text file that I didn't know how to separate. Thank you for a very helpful and professionally done tutorial.

@ong4848 11 жыл бұрын

Danny Rocks you are the best, easy to follow, learned more shortcut cos you keep repeating it & it memorialize automatically. That's to say I've solve my problems & improve my skill! Many Tanks Danny!.

@DannyRocksExcels 11 жыл бұрын

My pleasure. I am happy that I could help you with this Excel tutorial. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

My pleasure! I am happy that you found my KZfaq videos and that they are exactly what you are looking for. Thank you for your feedback on my teaching style. I work hard to make it as easy as possible for my students to learn Excel. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

My pleasure Tripp ! I am happy that my Excel Tutorial helped you. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

My pleasure Matt. I am happy that I could help you with my Excel tutorial. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

My pleasure! I am happy that you enjoy my tutorials and my teaching style. Thanks for adding your comments. Danny Rocks The Company Rocks

@andrewcunningham4086 11 жыл бұрын

Quick and simple. I really enjoy your lessons. I work in a GIS field and often find these excel functions are missed in our instruction but help a lot. Thank you so much!

@DannyRocksExcels 11 жыл бұрын

Thank you very much. I am pleased that you enjoyed my Excel Tutorial. Danny Rocks The Company Rocks

@DannyRocksExcels 12 жыл бұрын

Thank you! I am pleased that you enjoyed my tutorial. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

Great! I am pleased that my tutorial saved you so much time. Thanks for adding your comment. Danny Rocks The Company Rocks

@DannyRocksExcels 14 жыл бұрын

@XTOMMY1968X Hi Mark - Thank you very much for your nice comments. I am pleased that I have been able to help you. Danny Rocks The COmpany Rocks

@wheelsracer4057 11 жыл бұрын

Thank you Danny, for the second time you have delivered What I'm exactly searching for! your way of teaching is matching the students wants (you are delivering what we want) :-)

@pramilatawade1596 10 жыл бұрын

I am using this video today & learn new. Thank u.

@Howard10Howie 12 жыл бұрын

Sweet video! you were able to help me out just by watching 2:30 into your video. Thank you for taking your time to post such and informative video.

@omahahaha 12 жыл бұрын

Thanks for sharing this. Helped me out at work just now.

@RoshiGaming 5 жыл бұрын

Helped me out, over 9 years later! Thanks!

@DannyRocksExcels 11 жыл бұрын

My pleasure! I am delighted that my Excel tutorial will now save you "tons of time." Thanks for adding your feedback. Danny Rocks The Company Rocks

@Preetham324 12 жыл бұрын

A good video for transpose of rows and columns..thank u:-)

@alfonsourquidez8871 5 жыл бұрын

This was SO helpful. Thanks a bunch!

@mindaz3 12 жыл бұрын

Thank You, this video helped me a lot.

@DannyRocksExcels 14 жыл бұрын

@mark91345 Thank you Mark. I am very pleased that my explanation was helpful to you. Danny Rocks The Company Rocks

@HerbSilverman5 10 жыл бұрын

Got it! Thank you very much for your guidance.

@canderson124 11 жыл бұрын

Lifesaver! Thanks a ton for sharing this tutorial!

@motv8ed 14 жыл бұрын

Very well done, thank you very much.

@Pinkbuttafly08 11 жыл бұрын

Thank you so much for making this video, I had a batch of leads that needed to be converted from a txt file to excel and this is exactly what I was looking for and it worked like a charm, I'm glad I learned something new :)

@DannyRocksExcels 12 жыл бұрын

Thank you for adding your comment. I am pleased that I could help you. Danny Rocks The Company Rocks

@RitchieMatos 11 жыл бұрын

that helps a lot. Thank you so much

@KristianEsteban 13 жыл бұрын

Thanks!! Helped alot!

@dehash666 4 жыл бұрын

Thank you.

@XTOMMY1968X 15 жыл бұрын

Danny thanks for this, well done !!!!

@rbalker 10 жыл бұрын

Thank you, thank you, thank you. You helped me do in seconds what would have taken me and hour.

@winchcable2282 9 жыл бұрын

So helpful thank you so much! still works on excel 2013

@Roberteh3 13 жыл бұрын

This was extremely helpful, thank you so much.

@DannyRocksExcels 14 жыл бұрын

@ceaser412 Thank you very much for writing to me. I am very pleased that you enjoyed my video. Danny Rocks The Company Rocks

@417Pat 11 жыл бұрын

thank you so much! this was so helpful, JUST when i needed it! :) im such a n00b!

@OxygenGR 4 жыл бұрын

Thank you bro, you saved me :D

@DannyRocksExcels 11 жыл бұрын

THank you!I am so happy that I could help you with my tutorial, Re: Being a "Newbie" - We all started out as "newbies," so just stick with it. Sooner or later, everything will start to "click." Danny Rocks The Company Rocks

@compprufus 11 жыл бұрын

Hi, now I appologize for late response - I don't check YT mailbox that often. Anyway, I have looked up your Query video and yes it answere my question. Sadly I didn't have to ask for help, because I have already seen it befoe but didn't think about the problem the way I should. However, and here I think that Excel is complex software nad it should be able to do so, putting data in Excel isn't the only problem. As you have shown I too wanted to import stock data.

@ALMANZOR04 12 жыл бұрын

Yes you ROCK! thank you

@EZBreezie76 5 жыл бұрын

Amazingly helpful! Thank you!

@devilsdante 9 жыл бұрын

Thank you very much! awesome lesson!

@DannyRocksExcels 13 жыл бұрын

@KristianEsteban It is my pleasure. Glad that I could help you. Thanks for writing to tell me. Danny Rocks The Company Rocks

@jross7277 6 жыл бұрын

Wow this was a very good video. Exactly what I needed.

@DannyRocksExcels 12 жыл бұрын

@Preetham324 Thank you. I am pleased that you enjoyed my video tutorial. Danny Rocks The Company Rocks

@albayaams 7 жыл бұрын

Very useful lessons, Amazing, Thanks a lot

@ThomasFederico 7 жыл бұрын

Thank you very much. I could import my program list and organize it the way I wanted. It was simple and very effective +1

@DannyRocksExcels 14 жыл бұрын

@Danoli95 Thank you. I appreciate you taking the time to write to me. I am pleased that I helped you. Danny Rocks The Company Rocks

@guest_of_randomness 3 жыл бұрын

helps so much, thx!!!

@sadgyrlhush 11 жыл бұрын

LOVE YOU!

@DannyRocksExcels 12 жыл бұрын

@David52192 Thank you for adding your comments. Danny Rocks The Company Rocks

@miguelpeixoto4959 7 жыл бұрын

Thanks very much! Really useful!

@DannyRocksExcels 13 жыл бұрын

@FazWardak88 I am pleased that I could help you with my video. Thank you for writing. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

My pleasure. I am happy that I was able to give you the information that you needed. Excel is a marvelous program - so powerful. Thanks for taking the time to add your comment. Danny Rocks The Company Rocks

@joyswizz 10 жыл бұрын

whewww just need it and perfect tutorial...thank a lot

@bobbyd7704 9 жыл бұрын

Danny Rocks - Rocks!! thanks!

@DannyRocksExcels 13 жыл бұрын

@Roberteh3 My pleasure! Thank you for writing to tell me that I helped you. Danny Rocks The Company Rocks

@TracyAllenVideos 7 жыл бұрын

It worked in 2017! Lol! Thank you! You ROCK for sure!

@HCEveningWalk 7 жыл бұрын

Thank you for the tutorial! :)

@DannyRocksExcels 11 жыл бұрын

Thank you for your lovely "Portuguese / Brazilian" comment! I deeply appreciate your compliment. Danny Rocks The Company Rocks

@DannyRocksExcels 12 жыл бұрын

@mindaz3 Thank you for letting me know that I was able to help you with this Excel procedure. It is my pleasure to help my viewers whenever possible. Danny Rocks The Company Rocks

@trippjones14 11 жыл бұрын

very good. Thanks a million!!!!!!!!!

@xiaoningwang8169 9 жыл бұрын

very useful. Thanks a lot.

@nguyenvanthuy45cdt 10 жыл бұрын

it is really useful, thank you so much!

@mubarikhayat8524 5 жыл бұрын

LEGEND THANK YOU!

@gloriaaseru2966 6 жыл бұрын

Thanks a lot! Very helpful

@zeinabazadbakht4580 10 жыл бұрын

This is great; thanks a looooooooooot

@ceaser412 14 жыл бұрын

thank u very much. Its too gud Danny Rocks....!!!!

@larsjuly 11 жыл бұрын

nice tutorial...

@TiphanieJamison 9 жыл бұрын

#YAY Right on time. Thank you!

@ruchichauhan103 3 жыл бұрын

Thanks a lot..

@staciinde23 8 жыл бұрын

Thank you!!

@deth6ex670 8 жыл бұрын

Thank you so much :)

@DannyRocksExcels 11 жыл бұрын

I apologize for the delay in responding. Somehow I missed this message notification. If I understand your question, use File - Save As - (file type) CSV (Comma Delimited). Let me know if this works for you. Danny Rocks The Company Rocks

@Luciano_mp 11 жыл бұрын

Obrigado.

@DannyRocksExcels 11 жыл бұрын

Nichole - Without seeing your data, I can only guess where the problem exists. 1) What is your source data - where does it exist? 2) If, at all possible, get it into a program such as MS Word and save it as a CSV File (Comma Separated Values). You need to be able to separate the "fields" - the columns - using a "delimiter" such as a Comma, a Space, or the Tab key. 3) Once this file is saved as a TEXT file, you can use the techniques I demonstrate in this video. Danny Rocks The Company Rocks

@DannyRocksExcels 11 жыл бұрын

I apologize for the delay in responding. If at all possible, perform a Web Query in Excel. This gives you much more flexibility than with Copy and Paste Special, There are some factors to consider - for example, is the web data in an HTML Table. Search for my KZfaq Tutorial - How to Perform a Web Query in Excel. Let me know if this suggestion works for you. Danny Rocks The Company Rocks

@min131322 9 жыл бұрын

Thank you :)

@DannyRocksExcels 11 жыл бұрын

I apologize for the delay in responding. Without seeing your data, I can only suggest these guidelines: 1) In order for Excel to correctly store Dates and Times, these cells MUST be entered as NUMBERS 2) In a blank Excel Worksheet, you can tell if a Date or Time has been correctly entered: Numbers align to the Right side of the cell; Text labels align to the Left. Forget about formatting cells - ensure that the cells contain Numeric Values! Danny Rocks The Company Rocks

@compprufus 11 жыл бұрын

But I also wanted to automaticly put the data every day in different columns so in a matter of for example 10 days I could easily see the price development. Anyway thank you for you helpful advice.

@idkuthinkofone 10 жыл бұрын

thanks brah

@erindav1 7 жыл бұрын

thanks a bunch

@subscriptioncg 10 жыл бұрын

this was very helpful, how ever you didn't mention if your working with large amount of data how to organize them to print to fit on a whole page, well I tranpose the data it gives me many columns from A-AML with only 7 rows . I would like it to only go A-D then the rest going vertically in rows straight down because when I try to print it only gives me 4 columns PER page and then I'm waisting alot of paper space vertically. Please help me fix this. Thank you very much!

@katiehoolahan5735 4 жыл бұрын

I have a text file that when imported has too many rows to contain all of the data - is there a way to change the columns/rows around in import so I don't lose data?

@pixxelwizzard 3 жыл бұрын

How have I worked with Excel for years and never learned about the "transpose" feature? Thank you!

@adriel4040 5 жыл бұрын

Is there a way to reference specific lines from a word document into an excel sheet so as to organize every piece of information in one place, preferably in such a way so that the text updates if the word document updates?

@Djkoolgeorge 11 жыл бұрын

thank you for the tip Danny ..I have a question: can you tell me how can I do an email vertical list from text format to make it with commas?I really need that..

@compprufus 11 жыл бұрын

Thank you very much for this valuable tutorial. Still I have a question about pasting. Do you have any idea how to force Excel (2007) to automaticaly paste data collected for example from a website in a plain text? I have to do so manualy and if the dataset is large it's quite laborious work.

@sureshotmcpro3469 5 жыл бұрын

Danny Rocks, What's Up.

@pulam 13 жыл бұрын

hi there...i have a problem to which i cant find a quick solution. I have a DB in excel, about 100,000 rows down in 2 simple columns. I need to fit this for printing, and what i want is a way to get excel ; to fill a complete page with both columns across, and then jump to the next page, therefore having as much double columns per page as the page setup size allows, reducing the amount of pages to print...any suggestions?

@krishnafinancialconsulting531 8 жыл бұрын

Thanks.....

@EmMyBasseT 12 жыл бұрын

Hi Danny, nice vid but i have a problem here where my excel, the "transpose" seems invisible and cannot be click. please help me... :)

@MSCOCOISLUVINYOUTUB1 11 жыл бұрын

Hello Sr. I am trying to convert a list of about 500 emails into an Excel spreadsheet. When I copy and paste all of the emails go across the top on line 1. I need it to be in rows and columns. How can I achieve this?

@attariscommunication3468 7 жыл бұрын

Thanks

@bcdkonline 11 жыл бұрын

Your awesome teacher! I don't know another way to contact you so i use the comment. I have a problem, i have my .xlxs file with data like "4-6". I format the cells like text format in order to avoid the automatic date convert. But i wan this file to save it us .csv and upload it to my site. when i do this all my data turn to dates :/ Please i need help with this. I'm talking about many files with many data.

@DannyRocksExcels 11 жыл бұрын

You can find my email address on my opening page of almost every KZfaq video that I have posted. Danny Rocks The Company Rocks