leading zeros go missing

[EDUCATED MONKEY]

Win xp professional office xp 2003 ie 8
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Leading zero’s are missing when I format the cells as number, were as they are present if I format as text!
Normally this would no cause a problem, in this case however I am converting ISBN number firstly with a formula obtained from “dynamicorange.com” which work’s fantastically in either 10 digit to 13 digit and vice versa
The problem arises when the number is broken down into its component parts to place hyphens between the groups of numbers, again I have this working, provided the number does not have a leading zero
To give an example 1840462507 gets converted correctly to 1-84046-250-7 as well as many others
On the other hand if the number is say
0431098360 this ends up as 431098360 so when the mid function operates on it the wrong result is returned <o></o>
<o> </o>
I have not posted any code as it is all excel lefts rights mids Len’s nested IF AND etc so it would serve no purpose
I just need to be able to retain the leading zeros which will solve the problem
Any ideas ?

 

[shg]

You could format the cells as text.

Or leave them as numbers and use =text(A1, "0-00000-000-0") to convert to ISBN format.

 

[EDUCATED MONKEY]

Thanks i will give that a try asap regards pete

 

[EDUCATED MONKEY]

You could format the cells as text.

Or leave them as numbers and use =text(A1, "0-00000-000-0") to convert to ISBN format.

thanks that fixed it