Text to columns question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeB - 22 Jan 2008 15:50 GMT

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.

Reply to this Message

MikeB - 22 Jan 2008 16:06 GMT

> I have some data in the following format:
>
[quoted text clipped - 7 lines]
>
> 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

Reply to this Message

Sandy Mann - 22 Jan 2008 17:50 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>> I have some data in the following format:
>>
[quoted text clipped - 25 lines]
>
> Thanks

Reply to this Message

MikeB - 22 Jan 2008 19:36 GMT

> Try this on a copy of your data:
>
[quoted text clipped - 47 lines]
>
> > Thanks

Nice! Thanks.

Reply to this Message

Sandy Mann - 22 Jan 2008 22:31 GMT

I'm glad that it worked for you.

Signature

Regards,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>> Try this on a copy of your data:
>>
[quoted text clipped - 49 lines]
>
> Nice! Thanks.

Reply to this Message