Link to home
Create AccountLog in
Avatar of Tony Giangreco
Tony GiangrecoFlag for United States of America

asked on

Creating a spreadsheet from a text list pasted into Excel

I have a list if data that comes off a text based property list. I haven't done any macro development but I need  a macro that make a new worksheet in the Excel File, copies data from one worksheet and pastes it into the appropriate column in a new worksheet.

Attached is a sample of the text list pasted into excel.  The list pasted into Excel has 13,740 rows.

Below is a layout I'm trying to get this data formatted into in a 2nd worksheet.

Sale date      Case#  Opening Bid   Lender                        Address                     Attorney
6/1/2013      12345      50,000      Avenue Mortgage      1 Main St, My Town  John Lawyer

How do I write this macro?
Excel-text-list.xlsx
SOLUTION
Avatar of Danny Child
Danny Child
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
it might be interesting to see the actual text file rather than what has been loaded into excel... grasping at straws perhaps, with a good few rows shown in it. I am thinking carriage returns for example. and the colon looks reliable..
Avatar of Tony Giangreco

ASKER

Yes, I agree it's ugly. I can normalize it into a CSV file, but then how do I create csv data that represents rows and change it to represent columns so I can import it into a new spreadsheet?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The data contains personal information that cannot be posted here, but I've modified part of it. It's attached.
Raw-data.xlsx
Please post a sanitized text file.  Better to work with something as close to the source as possible.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi  byundt, I appreciate your help. I'm not familiar with VBA. Where do I save it and how do I run it?
not familiar with VBA: even more interesting for us to see a sanitised raw text file - the problem with giving us the results already in Excel is you are forcing the exclusion of  various methods Excel (at the least) has for processing the importation of text based data.
Yes, I had to sanitize it before posting. I'm sure you understand the importance of not uploading personal info.

Any suggestions on how to run that VB code?
We are not understanding each other here.

You are uploading an xlsx file.

We are asking for the file from which you are taking the data, BEFORE it goes in to Excel - and sanitised of course. Sorry I myself cannot help with VBA, which is probably your best tool for a solution in the end with this one.
Hi byundt, I was able to get the code to run. It worked great. I'm going to review it further and see if I need any adjustments.
Thanks for the help. It worked perfectly.