Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Split (Access 2002)

    Hi,

    I have a query relating to data splitting. I have received an excel spreadsheet which contains a number of records. In field 1 I have a complete name and address and ideally want to split this into seperate fields so I can produce address labels. Oddly enough, the first name is prefixed by a ' and then a space so I also need to chop this off as well. I am very unfamilar with code so was wondering if there is some way to do this with queries? I subsequently need to capitilise everything but have queries for this that have been kindly provided through this forum in the past.

    I have attached a copy of the raw data file and the ideal data split. I would really appreciate help on this one.

    Thanks...Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Split (Access 2002)

    You don't really need code to split the data.
    First, make a backup copy of the workbook.
    Open the workbook, and select the cells with the data (from B2 down as far as necessary).
    Select Data | Text to Columns...
    Specify Delimited, then click Next.
    Tick the check box for Space as delimiter, then click Finish.
    Do a visual check for consistency - the data will be split at *every* space, so if there is a space in the last name or street name, the data will shift columns.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Split (Access 2002)

    Beautiful - thanks!

Posting Permissions

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