Left() or Mid() question

[Presto]

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

 

[Douglas J. Steele]

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.

 

[Presto]

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.

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

(no e-mails, please!)

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

 

[Douglas J. Steele]

Each team in its own table doesn't sound correct either.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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

(no e-mails, please!)

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

 

[Presto]

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.

Each team in its own table doesn't sound correct either.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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

(no e-mails, please!)


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