Creating a spreadsheet from a text list pasted into Excel

Posted on 2013-05-16
Medium Priority
Last Modified: 2013-05-22
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?
Question by:Tony Giangreco
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Assisted Solution

by:Danny Child
Danny Child earned 200 total points
ID: 39173114
that data set is pretty horrible - blank rows, no clear demarcation between the data, variable lengths of fields and so on.

Is there any chance of a slightly friendlier file format?  ie CSV, TSV

you can extract some of the data by using formulas - this works for the cells that have the data and the heading in the same cell

Expert Comment

by:Anthony Mellor
ID: 39173190
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..
LVL 25

Author Comment

by:Tony Giangreco
ID: 39173221
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?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Assisted Solution

by:Anthony Mellor
Anthony Mellor earned 200 total points
ID: 39173227
I rather meant let us see the source file before it finds its way into excel; looking for things like whether the spacings are fixed.. what if we delete all the legends (headings) are we left with data that is 100% defined by where it is? Also whether there may be other hidden delimiters in the source file, such as <CR>, just exploring what the end of line EOL code might be.

to answer your question I can see the VBA mavens solving that with a FOR loop, if the locations are fixed.
LVL 25

Author Comment

by:Tony Giangreco
ID: 39173277
The data contains personal information that cannot be posted here, but I've modified part of it. It's attached.
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39173511
Please post a sanitized text file.  Better to work with something as close to the source as possible.
LVL 81

Accepted Solution

byundt earned 1600 total points
ID: 39173743
Assuming that the data is like the sample .xlsx file, here is a macro to produce the requested table on Sheet2:
Sub Normalizer()
Dim cel As Range, rg As Range, targ As Range
Dim i As Long, j As Long, k As Long, n As Long
Dim s As String, ss As String
Dim v As Variant, vData() As Variant
With ActiveSheet
    Set rg = .Range("A1")   'First cell with data
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))    'All the data in that column
End With
n = Application.CountIf(rg, "Sale Date:*")
ReDim vData(1 To n, 1 To 6)
For Each cel In rg.Cells
    s = UCase(cel.Value)
    If s <> "" Then
        j = InStr(1, s, ":")
        ss = Application.Trim(Mid(s, j + 1))
        If s Like "SALE DATE:*" Then
            i = i + 1
            If ss <> "" Then vData(i, 1) = CDate(ss)
        ElseIf s Like "CASE*:*" Then
            vData(i, 2) = CStr(ss)
        ElseIf s Like "OPENING BID:*" Then
            If ss <> "" Then vData(i, 3) = CDbl(ss)
            If Application.CountIf(cel.Resize(3, 1), "*:*") = 1 Then vData(i, 4) = cel.Offset(2, 0).Value
        ElseIf s Like "PROPERTY ADDRESS:*" Then
            If InStr(1, cel.Offset(1, 0).Value, ":") = 0 Then vData(i, 5) = cel.Offset(1, 0).Value
        ElseIf s Like "ATTORNEY:*" Then
            If InStr(1, cel.Offset(1, 0).Value, ":") = 0 Then vData(i, 6) = cel.Offset(1, 0).Value    'Ignore the number on the line after attorney's name
        End If
    End If
With Worksheets("Sheet2")
    Set targ = .Cells(.Rows.Count, 1).End(xlUp)
End With
If targ.Row = 1 Then
    targ.Resize(1, 6).Value = Array("Sale date", "Case#", "Opening Bid", "Lender", "Address", "Attorney")
    targ.Resize(1, 6).Font.Bold = True
End If
Set targ = targ.Offset(1, 0).Resize(n, 6)
targ.Columns(2).NumberFormat = "@"
targ.Value = vData
End Sub

Open in new window

LVL 25

Author Comment

by:Tony Giangreco
ID: 39174448
Hi  byundt, I appreciate your help. I'm not familiar with VBA. Where do I save it and how do I run it?

Expert Comment

by:Anthony Mellor
ID: 39174500
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.
LVL 25

Author Comment

by:Tony Giangreco
ID: 39174520
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?

Expert Comment

by:Anthony Mellor
ID: 39174611
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.
LVL 25

Author Comment

by:Tony Giangreco
ID: 39174637
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.
LVL 25

Author Closing Comment

by:Tony Giangreco
ID: 39187330
Thanks for the help. It worked perfectly.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question