Excel 2007 text sorting problem

  • Thread starter Thread starter dwightd
  • Start date Start date
D

dwightd

I apologize in advance for decidedly noob lingo...

In short, Excel is doing a very odd sort of last name/first name
columns in a worksheet containing data from two separate lists.
Here's an example:

Sandra H Bailey
Ward Bailey
B B BAILEY
BARBARA BAILEY
BARBARA BAILEY
DEBBIE BAILEY
ELEANOR BAILEY
ELOISE BAILEY
JAMES BAILEY
JANET BAILEY
JANET BAILEY
JANET BAILEY
JESSIE BAILEY
JIMMIE BAILEY
JOHN S BAILEY
JONNIE A BAILEY
JOY BAILEY
KATHRYN BAILEY
MYRA E BAILEY
NANCY BAILEY
THERESE BAILEY
WINA SPARKS BAILEY

The first two entries are from a list exported from a proprietary
database in either .xls or .csv. (I have tried both formats.) The
other list (all caps) I received in .csv.

I have had to concatenate some columns, and do some find/replace to
clean up the lists (e.g., removing punctuation from names and
addresses, removing prefixes from first names, etc.) Where formulas
are used, I've copied and pasted as values.

To combine the lists, I've used get external data/text for the .csv,
and have copied and pasted the other after formatting it so columns
match.

It's probably something obvious... Help?!

DwightD
 
Not sure I understand. Do you mean that the last name did not appear
in the post? If so, last name is "Bailey" as in the rest of the list.
 
You have several non-breaking space characters (code 160) immediately
before "Bailey". To get rid of them, assuming your last names are in
column B, put this in a helper column:

=SUBSTITUTE(B1,CHAR(160),"")

and copy down. You can do a similar thing for column A names.

Alternatively, you can highlight columns A and B and then Find/Replace
(CTRL-H):

Find what: Alt-0160
Replace with: leave blank

Then click Replace All, where Alt-0160 means hold the Alt key down
while you type 0160 on the numeric keypad.

Your sort should then work in the way you expect.

Hope this helps.

Pete
 
There are probably extra spaces involved.



Regards,


Peo Sjoblom

Not sure I understand. Do you mean that the last name did not appear
in the post? If so, last name is "Bailey" as in the rest of the list.
 
Hi,

You could also apply the =TRIM(A1) function and it might clean up the
problem, and if that doesn't work try =CLEAN(A1)
Put these formulas in a new column and copy them down. Paste as values.

Trim takes out all leading or trailing spaces and all but one space between
each work.
Clean removed some hidden characters.
If neither of these work and the example that you got using SUBSTITUTE
doesn't work then you can use the
=CODE(LEFT(A1)) command to find out if there is a non-standard character at
in the first position. Then you can use the SUBSTITUTE function as described
earlier with the code number.
 
Back
Top