SPLIT INFO

[Guest]

I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS IN
ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO CELLS

THANKS IN ADVANCE FOR YOUR HELP

SJW

 

[Rick B]

Please stop shouting at us (turn off your caps lock).

If you will search, you'll find numerous previous posts that address this
topic. Here is a portion of one of those posts...

You can add a new column to your query for the first name, and a column for
the last name. You can then use the values from the query in your forms,
reports, and other queries. I'd probably make this into an update query and
clean up my table and maintain the two fields from this point forward and
delete the combined field.

FName: Left([Name],InStr([Name],",")-1)

LName: Mid([Name],InStr([Name],",")+1)

 

[Guest]

Add FirstName and LastName fields to the table and then run an update query
to populate them with values parsed from the existing field, assumed to be
called FullName in this example, e.g.

UPDATE YourTable
SET FirstName = MID(Fullname,INSTR(FullName," ")+1),
LastName = LEFT(FullName, INSTR(FullName,",")-1);

This assumes all the values in the FullName field are in exactly the same
format as in your example , i.e. the last name followed by a comma, then a
space, then the first name.

Remember that if table or column names have spaces or other special
characters in them you need to wrap them in square brackets [like this].

BTW spreadsheets have cells, database tables have rows and columns (aka
rather inaccurately as records and fields). A database is a very different
animal from a spreadsheet.

Ken Sheridan
Stafford, England

 

[Guest]

RICK, THANKS FOR YOUR HELP. ALL CAPS FOR GUYS THAST ARE 70 AND DON;T SEE SO
WELL SORRY TO OFFEND YOU

SJW

Please stop shouting at us (turn off your caps lock).

If you will search, you'll find numerous previous posts that address this
topic. Here is a portion of one of those posts...

You can add a new column to your query for the first name, and a column for
the last name. You can then use the values from the query in your forms,
reports, and other queries. I'd probably make this into an update query and
clean up my table and maintain the two fields from this point forward and
delete the combined field.

FName: Left([Name],InStr([Name],",")-1)

LName: Mid([Name],InStr([Name],",")+1)


--
Rick B

I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS
IN
ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
CELLS

THANKS IN ADVANCE FOR YOUR HELP

SJW

 

[Rick B]

NO PROBLEM



--
Rick B

RICK, THANKS FOR YOUR HELP. ALL CAPS FOR GUYS THAST ARE 70 AND DON;T SEE
SO
WELL SORRY TO OFFEND YOU

SJW

Please stop shouting at us (turn off your caps lock).

If you will search, you'll find numerous previous posts that address this
topic. Here is a portion of one of those posts...

You can add a new column to your query for the first name, and a column
for
the last name. You can then use the values from the query in your forms,
reports, and other queries. I'd probably make this into an update query
and
clean up my table and maintain the two fields from this point forward and
delete the combined field.

FName: Left([Name],InStr([Name],",")-1)

LName: Mid([Name],InStr([Name],",")+1)


--
Rick B

I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME
IS
IN
ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
CELLS

THANKS IN ADVANCE FOR YOUR HELP

SJW