format cell type

  • Thread starter Thread starter kenkcj
  • Start date Start date
K

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.
 
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
 
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.
 
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
 
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
 
Multiplying by one instead of the double minus worked great, thank you both
for your time and input!
 
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 -
Click to expand...
 
Back
Top