I am needing some help in formatting numbers with a - as a divider.
Most numbers are 123456-0123, but I would like to not show the leading
0 after the -

I don't think you can do it with formatting but you can with a formula, one
way would be
=IF(A1<0,LEFT(A1,FIND("-",A1))&RIGHT(A1,LEN(A1)-FIND("-",A1)-1),"")
then copy paste special values to lose the formulas,
Regards,
Alan.
wrote in message
ups.com...
I am needing some help in formatting numbers with a - as a divider.
Most numbers are 123456-0123, but I would like to not show the leading
0 after the -

How can I ignore the 0 if there are 2 after the hyphen. Ex 123.0012

Do you want to convert 123456-0012 to 123456.12?

As text:
=IF(A1<0,LEFT(A1,FIND("-",A1)-1)&"."&--(MID(A1,FIND("-",A1)+1,255)))



dysart316 wrote:

How can I ignore the 0 if there are 2 after the hyphen. Ex 123.0012

--

Dave Peterson

Hi Dave,
Good one but could you please explain how that works? I know it does as I
tried it but I don't understand how it removes any number of leading zero's.
It has to be the +1,255 that does it, but how?
Regards,
Alan.
"Dave Peterson" wrote in message
...
Do you want to convert 123456-0012 to 123456.12?

As text:
=IF(A1<0,LEFT(A1,FIND("-",A1)-1)&"."&--(MID(A1,FIND("-",A1)+1,255)))



dysart316 wrote:

How can I ignore the 0 if there are 2 after the hyphen. Ex 123.0012

--

Dave Peterson

MID() is a string manipulation function. Look in help to see what the
parameters of the function are.
-- is a double unary minus, which converts the string to a number (and in
doing so it will lose leading zeroes).
& is the concatenate operator.
--
David Biddulph

"Alan" wrote in message
...
Hi Dave,
Good one but could you please explain how that works? I know it does as I
tried it but I don't understand how it removes any number of leading
zero's. It has to be the +1,255 that does it, but how?
Regards,
Alan.
"Dave Peterson" wrote in message
...
Do you want to convert 123456-0012 to 123456.12?

As text:
=IF(A1<0,LEFT(A1,FIND("-",A1)-1)&"."&--(MID(A1,FIND("-",A1)+1,255)))



dysart316 wrote:

How can I ignore the 0 if there are 2 after the hyphen. Ex 123.0012

--

Dave Peterson

And the +1 moves one character to the right of the hyphen.

And the 255 was just a big enough number to retrieve a bunch of characters. I'm
betting that your strings don't approach that length!

Alan wrote:

Hi Dave,
Good one but could you please explain how that works? I know it does as I
tried it but I don't understand how it removes any number of leading zero's.
It has to be the +1,255 that does it, but how?
Regards,
Alan.
"Dave Peterson" wrote in message
...
Do you want to convert 123456-0012 to 123456.12?

As text:
=IF(A1<0,LEFT(A1,FIND("-",A1)-1)&"."&--(MID(A1,FIND("-",A1)+1,255)))



dysart316 wrote:

How can I ignore the 0 if there are 2 after the hyphen. Ex 123.0012

--

Dave Peterson

--

Dave Peterson