format cell type

[kenkcj]

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.

 

[Sandy Mann]

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

--
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

 

[kenkcj]

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.

 

[Pete_UK]

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

 

[Sandy Mann]

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

--
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

 

[kenkcj]

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

 

[Pete_UK]

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!






- Show quoted text -