number formatting help

[dysart316]

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 -

 

[Alan]

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.

 

[dysart316]

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

 

[Dave Peterson]

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

 

[Alan]

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.

 

[David Biddulph]

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

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]

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]

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.

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.

 

[Alan]

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.