Creating custom list with a comma in it

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to sort a list of cities. I know how to creat a custom list however
there seems to be a bug in it. I am hoping somebody knows how to work around
this

Creating this list is simple:

CHICAGO
BALTIMORE
PHILADELPHIA
DALLAS
WASHINGTON

The problem comes when the same city is in multiple states so that the cell
actually says:
COLUMBUS, OH
COLUMBUS, GA
PORTLAND, OR
PORTLAND, ME

because internally the item separator is a comma, the custom list changes
these entries to:
COLUMBUS
OH
COLUMBUS
GA
PORTLAND
OR
PORTLAND
ME

Obviously when you do a data sort it doesn't find any of these values. I
tried creating the list programatically using the addCustomList but it did
the same thing. I've run out of things to try.

Any help would be appreciated.

Peter Richardson
 
I tried typing this list into a column in a worksheet.
COLUMBUS, OH
COLUMBUS, GA
PORTLAND, OR
PORTLAND, ME

Then I selected my range of cells
the I did tools|options|custom lists tab
and imported from the worksheet.

It seemed to work better than typing them in the dialog.
 
Did you actually try to use it in a sort? It looks ok in the Custom List
box. But the values don't match when trying to sort.
 
Yes, I did try a sort.

I think you missed a step in your sort.

Select your range
Data|Sort|Options button
specify your custom sort order.

But I did have to add that custom list via a range. If I typed them into the
dialog, then I had trouble.
 
Totally awesome!

The trick is to reorder the city names in the list before importing them
into the custom list. I had imported the list in alphabetic order and then
tried to move them to the order I wanted them. That killed it. However if
the city list is in the right order when importing then the sort works
perfectly.
 
Glad you got it working!
Totally awesome!

The trick is to reorder the city names in the list before importing them
into the custom list. I had imported the list in alphabetic order and then
tried to move them to the order I wanted them. That killed it. However if
the city list is in the right order when importing then the sort works
perfectly.
Click to expand...
 
Hi,
I tried importing but it does not work, there seems to be a bug.
I need to sort stuff in the below order:
1) COL
2) LTC
3) LTA
4) 1WO
5) 2WO
6) CPL

But is always gives me:
1) COL
2) LTA
3) 1WO
4) 2WO
5) CPL
6) LTC

Somehow LTC is always last.
Pls need help sorting this out.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Expression in Conditional macro 6
DELETE PART OF A FIELD IN ACCESS 3
deleting a comma from every cell in a column 2
Custom 0\.00 Format with Comma 4
Matching 2 list of data array, and return it as True if matched 1
Cascade Drop-Down Lists? 1
Custom list 255 character limit too small 1
I need the percentage of objects used in a drop-down box 2

Back
Top