Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Unhappy Unanswered: spliting data into multiple fields

    Hi all,
    i have about 38000 records of various fields.
    one of the field has two different type of datas embeded together. for example...

    field1 Field2
    TX77642 01234000000Gibson
    TX44568 01235000000dale
    CA23456
    so on and so forth, i can cut and paste the records of all fields in access easily.

    From the first field i would like to seperate the text with numbers
    Second field i want to seperate first five numebers in one field and name at the end into another field. for example
    field2 field3
    01234 gibson

    I have tried using queries but no luck here, since i am a newbie at access i am having hard time converting that. I am using access 2003.
    Any help would be greatly appriciated.

    Thanks alot

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, where are these records coming from, an Access table or from another program? Secondly, is these these records a one time event, or will you be doing this on a regular basis? Obviously cutting and pasting ain't gonna cut it with 38 k records!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wonder if access has an equivalent of regular expressions

    an approach would be to write a function which removes all digits, or all letters

    if you know your data is always prefixed / suffixed with the same characters then you could do it as an update query

    in nayevent I'd get the data into an access table, copy the table then update the copy. if all ok then overwrite the original....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2005
    Posts
    144
    Missinglinq, healdem:
    Thanks guys for replying, this data is in excel format and its all mixed up but to split it in appropriate fields its alot of work so i thought access is the best approach. Its a one time deal only i am getting this kind of info again. I have imported the whole excel sheet into access and except one row with -81 records other than that everything got imported perfect but all mixed up.

    I am not sure on how to make a update query or a query that will simply split the numbers with text. for example as i stated earliar instead of tx12345
    should be

    field 1 field2
    tx 12345

    please let me know if you guys think of any other way of doing it.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Well, you'll need to learn how to use a handful of text handling functions, wether you do it in Access or Excel. If the format of the data in each filed you want to split is not consistent, then you have your work cut out for you. Otherwise, playing around with functions like Mid$, Left$, Right$, Format, etc. will get you on your way. Oh, don't forget Iif for conditional changes.

    You can do it a piece at a time. Add the new fields to a query and fiddle with it until it looks correct, then switch the query to a "make table" and write the new table. Then make a second query to further process it. Not the most efficient, but works if yer new and only doing it one time anyway.

    tc

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also, ranjah, you might consider simply trying to import the Excel spreadsheet again. Something may have gone amiss the first time and may not occur the second time.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by ranjah
    Hi all,
    i have about 38000 records of various fields.
    one of the field has two different type of datas embeded together. for example...

    field1 Field2
    TX77642 01234000000Gibson
    TX44568 01235000000dale
    CA23456
    so on and so forth, i can cut and paste the records of all fields in access easily.

    From the first field i would like to seperate the text with numbers
    Second field i want to seperate first five numebers in one field and name at the end into another field. for example
    field2 field3
    01234 gibson

    I have tried using queries but no luck here, since i am a newbie at access i am having hard time converting that. I am using access 2003.
    Any help would be greatly appriciated.

    Thanks alot

    In your Excel use this to split the number out. For your first set of numbers on the left side do this:
    Code:
    =LEFT(B13,5)
    Then for the numbers on the right side do this:
    Code:
    =RIGHT(B13,4)
    Copy then entire Column of data you referred to way to the right of all your data. Then place both of those in cells to the far right of all your data if you can. If the numbers you have are in cell B13 you will do this:
    =Left(B13,2)
    =Right(B13,5)
    Copy those all the way down and it will split out the numbers for you just as you asked.

    have a nice one,
    BUD

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by ranjah
    for example...

    field1 Field2
    TX77642 01234000000Gibson
    TX44568 01235000000dale
    CA23456
    so on and so forth, i can cut and paste the records of all fields in access easily.
    Am I missing somehting here ? Why don't you just use the Text To Columns function in Excel (Data=>Text to columns) ? It's the Excel parsing tool.
    Chris

Posting Permissions

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