SORT

[Guest]

Hi everyone,
I have a question about sorting in Excel.

I didn't seperate my names with a first and last name field.

Now, I need to alphabetize by the last name....is there anyway to do this
when the first and last name are in the same field?

thanks
RW

 

[RBear3]

This is an Access newsgroup, not an Excel newsgroup. If you are using
Excel, I'd suggest posting to an Excel newsgroup.

Just FYI, you might want to indicate how the names are stores. If they are
LAST, FIRST you can separate the data very easily. If they are FIRST LAST,
then you are probably going to have much more difficulty since first and
last names could be made up of two or more words...

Mary Sue Adams
Linda de los Sotos
Rick Jones Jr.
etc.

 

[BruceM]

That sort of thing is probably possible with text functions, but if it is
you would probably do well to separate the data into separate fields. In
any case, you would do best to ask the question in an Excel group. This
newsgroup is for Microsoft Access, a relational database program.

 

[Guest]

thanks...you are always helpful.....i meant to say access, but it is first
and last.....im probably going to have to seperate them out manually....i
wasnt thinking when i first set it up.

 

[J]

Hello RW:

This is a discussion group on access, not excel. Therefore I will
tell you how to do this with Access!

Create a new access file.
Save the table in your excel spreadsheet to a new spreadsheet file,
one with JUST that table on it and have clear simple column headings
across row 1 (but name the column with the names in it "FullName")
Import your excel spreadsheet into an access table... pay attention
in the wizard and make sure everything is setup right (when in doubt
choose "text" as the field type for now).
Now, open your table in Access in design view.
Create two new fields "FirstName" "LastName", both text.
Save your table and close it.
Create an update query in design mode.
Choose your table as the query source.
Choose "Update Type" query.
Choose the fields FirstName and Lastname.
Under "Update to" for "FirstName" put:
Left([FullName],InStr([FullName]," ")-1)

Under "Update to" for "LastName" put:
Mid([FullName],InStr([FullName]," ")+1,20)

Run the Query.
Copy+Paste or export your table back to excel. Be sure to use "Paste
Special" and "Values" if you don't want to mess up formatting in your
original spreadsheet.

Move the "FirstName" and "LastName" columns to wherever you want...
then just run a sort on the LastName column. You can choose "Hide
Columns" on those after.

Hope that helps,
~J

 

[Guest]

dude u rock!...thanks!!!

Hello RW:

This is a discussion group on access, not excel. Therefore I will
tell you how to do this with Access!

Create a new access file.
Save the table in your excel spreadsheet to a new spreadsheet file,
one with JUST that table on it and have clear simple column headings
across row 1 (but name the column with the names in it "FullName")
Import your excel spreadsheet into an access table... pay attention
in the wizard and make sure everything is setup right (when in doubt
choose "text" as the field type for now).
Now, open your table in Access in design view.
Create two new fields "FirstName" "LastName", both text.
Save your table and close it.
Create an update query in design mode.
Choose your table as the query source.
Choose "Update Type" query.
Choose the fields FirstName and Lastname.
Under "Update to" for "FirstName" put:
Left([FullName],InStr([FullName]," ")-1)

Under "Update to" for "LastName" put:
Mid([FullName],InStr([FullName]," ")+1,20)

Run the Query.
Copy+Paste or export your table back to excel. Be sure to use "Paste
Special" and "Values" if you don't want to mess up formatting in your
original spreadsheet.

Move the "FirstName" and "LastName" columns to wherever you want...
then just run a sort on the LastName column. You can choose "Hide
Columns" on those after.

Hope that helps,
~J

Hi everyone,
I have a question about sorting in Excel.

I didn't seperate my names with a first and last name field.

Now, I need to alphabetize by the last name....is there anyway to do this
when the first and last name are in the same field?

thanks
RW

 

[Guest]

This worked great, I had the same problem and was trying to work it out. The
difference was that in my DB the last name was first and the first name was
last separated by a comma. It was easy enough to separate the names, but is
there a way to remove the comma after the last name.

example;
employeeName
Barker, William

becomes

EmployeeLastName EmployeeFirstName
Barker, William

using
Last name: Left([EmployeeName],InStr([EmployeeName]," ")-1)
First Name: Mid([EmployeeName],InStr([EmployeeName]," ")+1,20)

Thanks
CoachBarkerOJPW

dude u rock!...thanks!!!

Hello RW:

This is a discussion group on access, not excel. Therefore I will
tell you how to do this with Access!

Create a new access file.
Save the table in your excel spreadsheet to a new spreadsheet file,
one with JUST that table on it and have clear simple column headings
across row 1 (but name the column with the names in it "FullName")
Import your excel spreadsheet into an access table... pay attention
in the wizard and make sure everything is setup right (when in doubt
choose "text" as the field type for now).
Now, open your table in Access in design view.
Create two new fields "FirstName" "LastName", both text.
Save your table and close it.
Create an update query in design mode.
Choose your table as the query source.
Choose "Update Type" query.
Choose the fields FirstName and Lastname.
Under "Update to" for "FirstName" put:
Left([FullName],InStr([FullName]," ")-1)

Under "Update to" for "LastName" put:
Mid([FullName],InStr([FullName]," ")+1,20)

Run the Query.
Copy+Paste or export your table back to excel. Be sure to use "Paste
Special" and "Values" if you don't want to mess up formatting in your
original spreadsheet.

Move the "FirstName" and "LastName" columns to wherever you want...
then just run a sort on the LastName column. You can choose "Hide
Columns" on those after.

Hope that helps,
~J

Hi everyone,
I have a question about sorting in Excel.

I didn't seperate my names with a first and last name field.

Now, I need to alphabetize by the last name....is there anyway to do this
when the first and last name are in the same field?

thanks
RW

 

[John W. Vinson]

This worked great, I had the same problem and was trying to work it out. The
difference was that in my DB the last name was first and the first name was
last separated by a comma. It was easy enough to separate the names, but is
there a way to remove the comma after the last name.

example;
employeeName
Barker, William

becomes

EmployeeLastName EmployeeFirstName
Barker, William

using
Last name: Left([EmployeeName],InStr([EmployeeName]," ")-1)
First Name: Mid([EmployeeName],InStr([EmployeeName]," ")+1,20)

Similar except you need to look for the comma.

What InStr does is find the position within the first arbument of the second
argument: that is, InStr("Barker, William", " ") finds that the first blank
(the " " string) in the name is at the 8th character.

If you want to find the position of the comma, search for the comma:

LastName: Left([EmployeeName], InStr([EmployeeName], ",") - 1)

InStr finds that the comma is at the seventh character in the string; subtract
1 to get just the first six characters, "Barker".

For FirstName you can use

Trim(Mid([EmployeeName], InStr([EmployeeName], ",") + 1))

InStr will again find that the comma is at position 7; add 1 to get past the
comma; Mid() without the optional third argument will get the rest of the
string (" William" with a leading blank in this example); and finally Trim()
will trim off the leading blank. It's better to use Trim() than to explicitly
skip two characters - the comma and the blank - because in practice there will
be names in the data without the blank (and you don't want "Barker,William" to
end up with a firstname field of "illiam").

John W. Vinson [MVP]

 

[Guest]

Thanks that was just what I needed, now either way the full name is stored I
am able to separate it.

CoachBarkerOJPW

This worked great, I had the same problem and was trying to work it out. The
difference was that in my DB the last name was first and the first name was
last separated by a comma. It was easy enough to separate the names, but is
there a way to remove the comma after the last name.

example;
employeeName
Barker, William

becomes

EmployeeLastName EmployeeFirstName
Barker, William

using
Last name: Left([EmployeeName],InStr([EmployeeName]," ")-1)
First Name: Mid([EmployeeName],InStr([EmployeeName]," ")+1,20)

Similar except you need to look for the comma.

What InStr does is find the position within the first arbument of the second
argument: that is, InStr("Barker, William", " ") finds that the first blank
(the " " string) in the name is at the 8th character.

If you want to find the position of the comma, search for the comma:

LastName: Left([EmployeeName], InStr([EmployeeName], ",") - 1)

InStr finds that the comma is at the seventh character in the string; subtract
1 to get just the first six characters, "Barker".

For FirstName you can use

Trim(Mid([EmployeeName], InStr([EmployeeName], ",") + 1))

InStr will again find that the comma is at position 7; add 1 to get past the
comma; Mid() without the optional third argument will get the rest of the
string (" William" with a leading blank in this example); and finally Trim()
will trim off the leading blank. It's better to use Trim() than to explicitly
skip two characters - the comma and the blank - because in practice there will
be names in the data without the blank (and you don't want "Barker,William" to
end up with a firstname field of "illiam").

John W. Vinson [MVP]