How to Import a Text File into Excel and Change Rows to Columns
Вставка
- Опубліковано 25 сер 2024
- 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
@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
My pleasure! I am happy that you enjoy my tutorials and my teaching style.
Thanks for adding your comments.
Danny Rocks
The Company Rocks
My pleasure! I am happy that you found my UA-cam 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
Consider yourself liked. You just saved me sooooooooooo much time on my project. THANK YOU!!!
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.
My pleasure. I am happy that I could help you with this Excel tutorial.
Danny Rocks
The Company Rocks
Great! I am pleased that my tutorial saved you so much time.
Thanks for adding your comment.
Danny Rocks
The Company Rocks
Thank you for adding your comment. I am pleased that I could help you.
Danny Rocks
The Company Rocks
My pleasure Matt. I am happy that I could help you with my Excel tutorial.
Danny Rocks
The Company Rocks
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
My pleasure Tripp !
I am happy that my Excel Tutorial helped you.
Danny Rocks
The Company Rocks
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!
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!.
Thank you very much. I am pleased that you enjoyed my Excel Tutorial.
Danny Rocks
The Company Rocks
Thank you! I am pleased that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
@ceaser412
Thank you very much for writing to me. I am very pleased that you enjoyed my video.
Danny Rocks
The Company Rocks
@mark91345
Thank you Mark. I am very pleased that my explanation was helpful to you.
Danny Rocks
The Company Rocks
Thank you.
@Preetham324
Thank you. I am pleased that you enjoyed my video tutorial.
Danny Rocks
The Company Rocks
This was SO helpful. Thanks a bunch!
Helped me out, over 9 years later! Thanks!
@KristianEsteban
It is my pleasure. Glad that I could help you.
Thanks for writing to tell me.
Danny Rocks
The Company Rocks
@FazWardak88
I am pleased that I could help you with my video.
Thank you for writing.
Danny Rocks
The Company Rocks
I am using this video today & learn new. Thank u.
@David52192
Thank you for adding your comments.
Danny Rocks
The Company Rocks
Thanks for sharing this. Helped me out at work just now.
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) :-)
So helpful thank you so much! still works on excel 2013
Thank You, this video helped me a lot.
Got it! Thank you very much for your guidance.
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.
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.
Thank you for your lovely "Portuguese / Brazilian" comment!
I deeply appreciate your compliment.
Danny Rocks
The Company Rocks
@Roberteh3
My pleasure! Thank you for writing to tell me that I helped you.
Danny Rocks
The Company Rocks
A good video for transpose of rows and columns..thank u:-)
Lifesaver! Thanks a ton for sharing this tutorial!
Very well done, thank you very much.
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
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
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 :)
that helps a lot. Thank you so much
Thank you, thank you, thank you. You helped me do in seconds what would have taken me and hour.
@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
This was extremely helpful, thank you so much.
Danny thanks for this, well done !!!!
Thanks!! Helped alot!
@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
Yes you ROCK! thank you
Thank you very much! awesome lesson!
Amazingly helpful! Thank you!
Very useful lessons,
Amazing,
Thanks a lot
thank you so much! this was so helpful, JUST when i needed it! :) im such a n00b!
Thank you bro, you saved me :D
Thank you very much. I could import my program list and organize it the way I wanted. It was simple and very effective +1
helps so much, thx!!!
nice tutorial...
Wow this was a very good video. Exactly what I needed.
Thanks very much! Really useful!
Danny Rocks - Rocks!! thanks!
very good. Thanks a million!!!!!!!!!
It worked in 2017! Lol! Thank you! You ROCK for sure!
LOVE YOU!
Thank you for the tutorial! :)
very useful. Thanks a lot.
Thanks a lot..
whewww just need it and perfect tutorial...thank a lot
This is great; thanks a looooooooooot
it is really useful, thank you so much!
LEGEND THANK YOU!
Thanks a lot! Very helpful
thank u very much. Its too gud
Danny Rocks....!!!!
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
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
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..
#YAY Right on time. Thank you!
Thank you!!
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.
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?
Hi Danny, nice vid but i have a problem here where my excel, the "transpose" seems invisible and cannot be click. please help me... :)
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 UA-cam Tutorial - How to Perform a Web Query in Excel.
Let me know if this suggestion works for you.
Danny Rocks
The Company Rocks
Thank you so much :)
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.
How have I worked with Excel for years and never learned about the "transpose" feature? Thank you!
Obrigado.
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!
thanks brah
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?
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?
thanks a bunch
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?
Thank you :)
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
Thanks.....
Thanks
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.
how can I contact you for a specific guide for formatting?
Danny Rocks, What's Up.
You can find my email address on my opening page of almost every UA-cam video that I have posted.
Danny Rocks
The Company Rocks
bulk kyc text file convert excel file me kaise hoga
Hi Danny, I need your help please. How to convert data in different rows into one row.
Iftikhar Ahmed Arain You can use "CONCATENATE" function to Covert data in different rows into one row.