alphabetize ignoring hyphen

[lewisweiss]

i have a spreadsheet which i need to alphabetize. in column A are 4000
domain names. all of the domain names start with the word GLOBAL and then
another word. about 200 of them start with the word GLOBAL and then follow
with a hyphen before the word. i wish to alphabetize the entire spreadsheet
on the column A and IGNORE the hyphen so that the spreadsheet will be
alphabetized on column A by the word following the word GLOBAL. PLEASE HELP
HOW TO DO THIS? perhaps some macro formula can ignore the hyphen?

 

[OssieMac]

What about removing the hyphen? Is that a viable option? If so, use Find and
Replace. Enter a hyphen for find and space for replace and then you should be
able to do what you want.

 

[lewisweiss]

thanks but i need the hyphen to display, just ignore it when alphabetizing

 

[Mike H]

Hi,

You need a Helper Column, Say your data are in A1 down

In a column next to you data in B1 enter the formula
=MID(A1,10,LEN(A1))
This will extract everyting except the first 10 characters from A1. You may
need to alter the 10 to match your data. Drag the formula down as required.

Select all your data including the helper column and sort using the helper
column as the key.
Hide or delete the helper column.

Mike

 

[lewisweiss]

hi mike H,

thanks very very much.

i think this will work, but i have been trying and trying to enter the
formula into column B, but it will not respond. it just keeps putting the
same value in every row of column B.

puzzling, because the first time i entered the formula, the sort did seem to
work KINDA, but what happened was that some of the domains were lost. but
when i tried it again, i failed to be able to enter the formula.

btw, the data starts in column A row 3, and if i want the alphabetizing to
start after the hyphen and eliminate the first word GLOBAL then i am not sure
if i did it right after all.

an example of the domains without hyphen: GLOBALACCOUNTANT.COM

an example of the domains WITH hyphen: GLOBAL-ACCOUNT.COM

the formula i used was:

=MID(A3,7,LEN(A3))

i changed the A1 in your formula to A3 because my data starts in A3. i
changed the 10 in your formula to 7 because i want to alphabetize after
GLOBAL- but i just realized that this maybe wrong because in the case of a
GLOBAL without the hyphen, your formula will start one character later.

but when i pasted your formula into B column the first time, and then copied
it down the column, the result seemed OK in that the column B contained just
the word after the GLOBAL. but now i cannot replicate this for some reason.

puzzling.

thanks for your help. if you prefer, i can email you the spreadsheet so you
can see what i am trying to do.

warm regards,

Lewis Weiss
(e-mail address removed)

i am to paste this formula into the box B3 and then continue by pasting it
into every row below that, yes?

 

[Ron Rosenfeld]

i have a spreadsheet which i need to alphabetize. in column A are 4000
domain names. all of the domain names start with the word GLOBAL and then
another word. about 200 of them start with the word GLOBAL and then follow
with a hyphen before the word. i wish to alphabetize the entire spreadsheet
on the column A and IGNORE the hyphen so that the spreadsheet will be
alphabetized on column A by the word following the word GLOBAL. PLEASE HELP
HOW TO DO THIS? perhaps some macro formula can ignore the hyphen?

In a helper column, adjacent to your data, enter the formula:

=SUBSTITUTE(A1,"-","",1)

And fill down as far as required.

Sort on the helper column.

Then delete or hide the helper column.
--ron