Left() or Mid() question "Presto" <presto__NOSPAM[ at ]prestoweb.net> 12/3/2008 3:17:45 PM I have imported data from an Excel spreadsheet from another department (They are uncooperative and wont change their data structure...) Three of their fields contain name data formatted as lastname / lastname (this is a buddy list): Example: Team1 Team2 Team3 Smith/Jones Doe/Johnson Green/Smith I need to extract the names and separate them so I can put it in a new table. I cant work with the data in this format. I know how to use Left() or Right() to get a certain string, but I don't know how to get all data before-and not including the / , and anything after the / . This has to be set up to be done automatically since there are over 500 records to edit and no time to do it manually. I want to end up with two columns(for each team) and the names split up: Team1a Team1b Smith Jones

Re: Left() or Mid() question "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/3/2008 3:29:06 PM Left([Team1], InStr([Team1], "/") - 1) and Mid([Team1], InStr([Team1], "/") + 1) Hopefully you're intending to get rid of the repeated group when you port the data to Access. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message news:eRb2HpVVJHA.4148[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] >I have imported data from an Excel spreadsheet from another department > (They are uncooperative and wont change their data structure...) > > Three of their fields contain name data formatted as lastname / lastname > (this is a buddy list): > Example: > Team1 Team2 Team3 > Smith/Jones Doe/Johnson Green/Smith > > I need to extract the names and separate them so I can put it in a new > table. > I cant work with the data in this format. I know how to use Left() or > Right() > to get a certain string, but I don't know how to get all data before-and > not > including the / , and anything after the / . This has to be set up to > be done > automatically since there are over 500 records to edit and no time to do > it manually. > > I want to end up with two columns(for each team) and the names split up: > Team1a Team1b > Smith Jones > > > > >

Re: Left() or Mid() question "Presto" <presto__NOSPAM[ at ]prestoweb.net> 12/3/2008 4:01:01 PM Thanks again Doug! This works perfectly! Each team will be in it's own table. I will use this code in a "MakeTable" query. I am doing my best to normalize this data so we can re-work the tables. "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message news:%23x5jivVVJHA.4812[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > Left([Team1], InStr([Team1], "/") - 1) and Mid([Team1], InStr([Team1], > "/") + 1) > > Hopefully you're intending to get rid of the repeated group when you port > the data to Access. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message > news:eRb2HpVVJHA.4148[ at ]TK2MSFTNGP05.phx.gbl... >>I have imported data from an Excel spreadsheet from another department >> (They are uncooperative and wont change their data structure...) >> >> Three of their fields contain name data formatted as lastname / lastname >> (this is a buddy list): >> Example: >> Team1 Team2 Team3 >> Smith/Jones Doe/Johnson Green/Smith >> >> I need to extract the names and separate them so I can put it in a new >> table. >> I cant work with the data in this format. I know how to use Left() or >> Right() >> to get a certain string, but I don't know how to get all data before-and >> not >> including the / , and anything after the / . This has to be set up to >> be done >> automatically since there are over 500 records to edit and no time to do >> it manually. >> >> I want to end up with two columns(for each team) and the names split up: >> Team1a Team1b >> Smith Jones >> >> >> >> >> > >

Re: Left() or Mid() question "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/3/2008 5:13:36 PM Each team in its own table doesn't sound correct either. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message news:eKARTBWVJHA.2080[ at ]TK2MSFTNGP06.phx.gbl... [Quoted Text] > Thanks again Doug! This works perfectly! > Each team will be in it's own table. I will use this code in a "MakeTable" > query. > I am doing my best to normalize this data so we can re-work the tables. > > > "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message > news:%23x5jivVVJHA.4812[ at ]TK2MSFTNGP05.phx.gbl... >> Left([Team1], InStr([Team1], "/") - 1) and Mid([Team1], InStr([Team1], >> "/") + 1) >> >> Hopefully you're intending to get rid of the repeated group when you port >> the data to Access. >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> >> "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message >> news:eRb2HpVVJHA.4148[ at ]TK2MSFTNGP05.phx.gbl... >>>I have imported data from an Excel spreadsheet from another department >>> (They are uncooperative and wont change their data structure...) >>> >>> Three of their fields contain name data formatted as lastname / lastname >>> (this is a buddy list): >>> Example: >>> Team1 Team2 Team3 >>> Smith/Jones Doe/Johnson Green/Smith >>> >>> I need to extract the names and separate them so I can put it in a new >>> table. >>> I cant work with the data in this format. I know how to use Left() or >>> Right() >>> to get a certain string, but I don't know how to get all data before-and >>> not >>> including the / , and anything after the / . This has to be set up to >>> be done >>> automatically since there are over 500 records to edit and no time to do >>> it manually. >>> >>> I want to end up with two columns(for each team) and the names split up: >>> Team1a Team1b >>> Smith Jones >>> >>> >>> >>> >>> >> >> > >

Re: Left() or Mid() question "Presto" <presto__NOSPAM[ at ]prestoweb.net> 12/3/2008 10:00:42 PM I know I need one table of person's names, one table for teams, then assign persons to a team.... assign projects to the teams...eventually we'll get there. For now, at least I can import the existing data and work with it or at least convert it to something remotely useful. The problem is that the other departments send us garbage to work with. The origional db had only ONE table with about 50 fields. It was a table from hell. Everything was imported into the one table. They had one form with every field on it, and you had to scroll through every record to get to what you wanted. Sad.... very sad. I'm not an Access expert, but I know a **really bad** db when I see one. "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message news:ugBl7pWVJHA.5588[ at ]TK2MSFTNGP03.phx.gbl... [Quoted Text] > Each team in its own table doesn't sound correct either. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message > news:eKARTBWVJHA.2080[ at ]TK2MSFTNGP06.phx.gbl... >> Thanks again Doug! This works perfectly! >> Each team will be in it's own table. I will use this code in a >> "MakeTable" query. >> I am doing my best to normalize this data so we can re-work the tables. >> >> >> "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message >> news:%23x5jivVVJHA.4812[ at ]TK2MSFTNGP05.phx.gbl... >>> Left([Team1], InStr([Team1], "/") - 1) and Mid([Team1], InStr([Team1], >>> "/") + 1) >>> >>> Hopefully you're intending to get rid of the repeated group when you >>> port the data to Access. >>> >>> -- >>> Doug Steele, Microsoft Access MVP >>> http://I.Am/DougSteele >>> (no e-mails, please!) >>> >>> >>> "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message >>> news:eRb2HpVVJHA.4148[ at ]TK2MSFTNGP05.phx.gbl... >>>>I have imported data from an Excel spreadsheet from another department >>>> (They are uncooperative and wont change their data structure...) >>>> >>>> Three of their fields contain name data formatted as lastname / >>>> lastname >>>> (this is a buddy list): >>>> Example: >>>> Team1 Team2 Team3 >>>> Smith/Jones Doe/Johnson Green/Smith >>>> >>>> I need to extract the names and separate them so I can put it in a new >>>> table. >>>> I cant work with the data in this format. I know how to use Left() or >>>> Right() >>>> to get a certain string, but I don't know how to get all data >>>> before-and not >>>> including the / , and anything after the / . This has to be set up >>>> to be done >>>> automatically since there are over 500 records to edit and no time to >>>> do it manually. >>>> >>>> I want to end up with two columns(for each team) and the names split >>>> up: >>>> Team1a Team1b >>>> Smith Jones >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> > >