Sort by first capital

[jsid]

How would I get Excel to alphabetize cells in a column by the first capital letter in the cell?

For example,
...
Wyoming
...
at San Francisco
...

I need the spreadsheet to skip the word at and alphabetize by the first capitalized location (Also, it's always "at" so if it's easier, Excel could just ignore "at" and alphabetize the list)

Thanks.

[Tony Valko]

Are those dots in some of the cells?

[jsid]

No the dots are just to illustrate that those are two separate cells.

[Tony Valko]

Maybe you can do something like this.

Data Range

	A	B
2	Wyoming	Wyoming
3	at San Francisco	San Francisco
4	Colorado	Colorado
5	at New Mexico	New Mexico
6	Florida State	Florida State
7	at Pitt	Pitt

Enter this formula in B2 and copy down:

=MID(A2,IF(LEFT(A2,3)="at ",4,1),25)

Inspect the results to make sure they are what you want.

Then, convert the formulas to constants...

Select the entrie range of formulas in column B.
Right click>Copy
Right click>Paste Special>Values>OK

You can now sort on column B and you can delete the original data if so desired.

[Teethless mama]

Assuming your data start in A2

In B2: =MID(A2,MATCH(1,(CODE(MID(A2,ROW($1:$99),1))>=65)*(CODE(MID(A2,ROW($1:$99),1))<=90),),99)

Array formula press Ctrl+Shift+Enter, not just Enter
copy down as far as needed

Then sort in column B

[Tony Valko]

Assuming your data start in A2

In B2: =MID(A2,MATCH(1,(CODE(MID(A2,ROW($1:$99),1))>=65)*(CODE(MID(A2,ROW($1:$99),1))<=90),),99)

Using the ROW function like that makes the formula vulnerable to new row insertions.

Using the INDIRECT function is a more robust choice.

ROW(INDIRECT("1:99"))