Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create new spreadsheet (13 columns) but data has variable number columns

    Hi there,

    I have a pipe delimited .txt file to work with. It contains a list of around 6,000 records (one record per line)
    Some of the data headings are: Address 1, Address 2, Address 3, City State Postcode, DOB, File No:,
    Some of the entries contain errors, i.e. Address 1 and 2 data have been entered into Address 1.
    Is there any quick way to correct this type of thing using macros?

    Once I have correct data in each field, I then need to use this information as a very makeshift txt based database.

    Thanks for any help. The current delimited .txt file is constantly updated so I need to find a resuable workaround rather than a one-off fix or system.

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by ozgal View Post
    Hi there,

    I have a pipe delimited .txt file to work with. It contains a list of around 6,000 records (one record per line)
    Some of the data headings are: Address 1, Address 2, Address 3, City State Postcode, DOB, File No:,
    Some of the entries contain errors, i.e. Address 1 and 2 data have been entered into Address 1.
    Is there any quick way to correct this type of thing using macros?

    Once I have correct data in each field, I then need to use this information as a very makeshift txt based database.

    Thanks for any help. The current delimited .txt file is constantly updated so I need to find a resuable workaround rather than a one-off fix or system.
    Are you saying that certain records may not have the correct number of delimiters? This will give you a problem with automation, since it is easy to program the import and parsing of delimited records if there are always the same number of fields.

    Do you have a sample text file you could upload (suitable anonymized!)?

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Unless you have a guaranteed delimiter between the address entries it would be almost impossible to split an address into 2 parts. Do you have a carriage return in the fields that are mixed?

    cheers, Paul

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Perhaps this is what you are looking for

    It could be done if the second address consistently starts with the first instance of a number after a string of text. Ex: street number(numbers) followed by street name (text) followed by street numbers (numbers). How about something like this.

    Public Sub SplitAddress()
    'Look for the street number
    Address = Cells(1, 1).Value 'assign the entire address in cell A1 a variable "Address" 121 south street 1 Market street
    Streetnum = Val(Address) 'identify fisrt set of street numbers 121
    Remainder = Trim(Mid(Address, Len(Streetnum) + 1, Len(Address))) 'The rest of the total address minus the first street number south street 1 Market street

    For i = Len(Remainder) To 1 Step -1 'cycle the loop backwards the number of times as the number of characters in the streetname 28 to 1
    If Val(Right(Remainder, i)) <> 0 Then 'Search string for next number. If a number then the beginning of second address found Cycle until the number 1 before Market
    Address2 = Right(Remainder, i) 'get full second address 1 Market street
    Streetname = Left(Remainder, Len(Remainder) - i) 'get first street name south street
    Address1 = Streetnum & " " & Streetname 'first full address. Concatenate street number and street name 121 south street
    End If
    Next i

    Cells(1, 2).Value = Address1 'Assign cell B1 with Address1 121 south street
    Cells(1, 3).Value = Address2 'Assign cell C1 with Address2 1 Market street
    End Sub

    HTH
    Maud
    Attached Images Attached Images
    Last edited by Maudibe; 2012-11-07 at 00:14.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Thank you for all replies thus far.
    Unfortunately, the address fields do not always start with numbers or anything that is absolute across the board.
    The file has been converted from pdf format to xls (using third party software Able2Extract (professional version).
    I have noticed there are some small boxes in the pdf before it is converted.
    On reading up about this, these may indicate occurences of "text box overflow".
    I wonder if figuring out a way to rectify the text box overlow issue may fix the issue of the delimited entries being out of order once the pdf is converted to xls.
    The final outcome needs to be in pipe delimited txt format as it is used by another third party program as a database.
    Hopefully the above gives a better description of what I am working with and what I am aiming to create.
    Last edited by ozgal; 2012-11-08 at 21:46.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Can you provide a sample converted spreadsheet with the addresses as they now appear?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •