format cell type

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kenkcj - 30 Jul 2007 22:20 GMT

Hi everyone, in a spreadsheet, I have a complex data type that I want to
sort correctly. The format contains a prefix that contains both letters(A)
and numbers(0) followed by a suffix after a hyphen and another number.
Example:
000A0000-1
and
000A0000-10
The thing Excel is messing up in, is if the complete prefix (000A0000) is
the same, but a different number after the hyphen, it sorts it 1, 10, 11,
12, 13, ..., 2, 20, 21, 22, ... 3, etc.
I was wondering if anyone knew how to set up a custom format type that will
allow a suffix stored as text and the suffix after the hyphen stored as a
number. Thank you in advance.

Reply to this Message

Sandy Mann - 31 Jul 2007 00:06 GMT

I would use two helper columns. With your data in Column J, I put the
formula:

=LEFT(J1,FIND("-",J1)-1)

in K1 and copied down. Then in L1 the formula:

=--MID(J1,FIND("-",J1)+1,255)

and copy that down.

Finially I highlighted all three columns and sorted on Column K and then
Column L

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

> Hi everyone, in a spreadsheet, I have a complex data type that I want to
> sort correctly. The format contains a prefix that contains both letters(A)
[quoted text clipped - 9 lines]
> will allow a suffix stored as text and the suffix after the hyphen stored
> as a number. Thank you in advance.

Reply to this Message

kenkcj - 31 Jul 2007 15:40 GMT

Thank you for your timely response, these tools will definitely be helpful.
However, when I put the second formula for the L column, it was coming up
with errors. I removed the first "-" in the beginning of that formula so
that it read =-MID.. instead of =--MID.. and it seemed like it was working,
but all of the extension numbers came out as -1, -2, -11, etc. When I tried
to sort that information, it went in reverse order, seeing the numbers as
negatives instead. Please let me know what I'm doing wrong. Thanks again for
your help.

>I would use two helper columns. With your data in Column J, I put the
>formula:
[quoted text clipped - 24 lines]
>> will allow a suffix stored as text and the suffix after the hyphen stored
>> as a number. Thank you in advance.

Reply to this Message

Pete_UK - 31 Jul 2007 16:06 GMT

You need the double unary minus before the MID in order to convert the
extracted text into a number. An alternative would be to multiply by
1, like so:

=1*MID(J1,FIND("-",J1)+1,255)

Hope this helps.

Pete

> Thank you for your timely response, these tools will definitely be helpful.
> However, when I put the second formula for the L column, it was coming up
[quoted text clipped - 45 lines]
>
> - Show quoted text -

Reply to this Message

kenkcj - 31 Jul 2007 17:02 GMT

Multiplying by one instead of the double minus worked great, thank you both
for your time and input!

> You need the double unary minus before the MID in order to convert the
> extracted text into a number. An alternative would be to multiply by
[quoted text clipped - 65 lines]
>>
>> - Show quoted text -

Reply to this Message

Pete_UK - 31 Jul 2007 17:48 GMT

Glad to hear it - thanks for feeding back.

Pete

> Multiplying by one instead of the double minus worked great, thank you both
> for your time and input!
[quoted text clipped - 74 lines]
>
> - Show quoted text -

Reply to this Message

Sandy Mann - 31 Jul 2007 16:49 GMT

If Pete's answer does not solve your problem then post back stating the
exact error that it is throwing up.

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

Reply to this Message

Rate this thread: