Text to columns question

[MikeB]

I have some data in the following format:

Number firstname lastname schoolname

The first three fields are one word each. Schoolname can be more than
one word. I'm trying to parse the data using the data -> Text to
Columns wizard, but I cannot figure out how to tell the wizard that
there are only 4 space delimited columns in my data and not 5 for
those instances where the schoolname is two words.

Any help is appreciated. Thanks.

 

[MikeB]

I have some data in the following format:

Number firstname lastname schoolname

The first three fields are one word each. Schoolname can be more than
one word. I'm trying to parse the data using the data -> Text to
Columns wizard, but I cannot figure out how to tell the wizard that
there are only 4 space delimited columns in my data and not 5 for
those instances where the schoolname is two words.

Any help is appreciated. Thanks.

Actually, I got this fixed. Not sure if it was the best way to do it,
but I found a way to workaround.

I split the data into the 5 columns. Then I created a new column that
concatenated the two columns that contained the school name into one
column. Then (using Paste Special) I created a new colum containing
the values from the concatenated column and I deleted the unnecessary
columns.

Now I have another question

If I now use subtatals, is there a way to sort the result of the
subtotals so that the school with the most students in it appears
first? In other words, can I sort on the subtotals? I tried and it
sorts the underlying data.

Thanks

 

[Sandy Mann]

Try this on a copy of your data:

Immediately to the right of your data enter the formula:

=COUNTIF($C$2:$C$14,C2)

Where Column C is the School name, adjust the range to suit your data and
copy down.

Next highlight all your data including the above formula and sort in
decending order.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MikeB]

Try this on a copy of your data:

Immediately to the right of your data enter the formula:

=COUNTIF($C$2:$C$14,C2)

Where Column C is the School name, adjust the range to suit your data and
copy down.

Next highlight all your data including the above formula and sort in
decending order.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Nice! Thanks.

 

[Sandy Mann]

I'm glad that it worked for you.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk