number formatting help
 

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 -

number formatting help
 

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 -

number formatting help
 

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

number formatting help
 

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

number formatting help
 

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

number formatting help
 

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

number formatting help
 

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

number formatting help
 

Thanks for that,
I understand the LEFT, RIGHT, MID and & functions, you may not have noticed
but the first reply to the original query was mine. What I didn't know was
that the double unary minus would covert the text string to a number and
thereby lose the leading zero's, I've only ever used '--' in SUMPRODUCT
formulas. Presumably the 255 is just overkill incase there are dozens of
leading zero's. Very ingenious, we live and learn!
Regards,
Alan.
"David Biddulph" wrote in message
...
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

number formatting help
 

You're right there, nothing like that long. I think I understand it now how
it works with yours and David's replies. As I said, very ingenious and one
to note for future reference,
Thanks,
Alan.
"Dave Peterson" wrote in message
...
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