# hyphenated number format with leading zeros to the right of the hy

1. ## hyphenated number format with leading zeros to the right of the hy

I need to achieve a result like this: 12345-0001
However, the user does not want to type the leading zeros.

Help would be much appreciated!!!  Register To Reply

2. ## RE: hyphenated number format with leading zeros to the right of the hy

From your example I assume the numbers are of the form:

5 digits -000 1 digit

if the user just types in 123451 and the next cell has the formula:
=LEFT(A1,5)&"-000"&RIGHT(A1,1)
then it will display 12345-0001
just as you want.
_____________________________________
Gary's Student

"CJ" wrote:

> I need to achieve a result like this: 12345-0001
> However, the user does not want to type the leading zeros.
>
> Help would be much appreciated!!!  Register To Reply

3. ## RE: hyphenated number format with leading zeros to the right of the hy

Hi,

Enter the following formula in B1, assuming that A1 contains the
user-entered number.

=LEFT(A1,5)&"-"&RIGHT(10000+RIGHT(A1,LEN(A1)-5),4)

The formula will convert,

123451 to 12345-0001
1234512 to 12345-0012
12345123 to 12345-0123
123451234 to 12345-1234

If the user enters a number containing less than 6 digits, it would return
an error as #VALUE!

If you have several user-entered numbers in column A (eg., A1:A101) enter
the above formula in B1 and fil-in the formula down to B101.

Hope this helps,
Regards,
B. R. Ramachandran

"CJ" wrote:

> I need to achieve a result like this: 12345-0001
> However, the user does not want to type the leading zeros.
>
> Help would be much appreciated!!!  Register To Reply

4. ## RE: hyphenated number format with leading zeros to the right of th

Thank you so much and also for the fast response! I kind of figured I was
going to have do something to the cell next to it but couldn't figure it out.

"Gary''s Student" wrote:

> From your example I assume the numbers are of the form:
>
> 5 digits -000 1 digit
>
> if the user just types in 123451 and the next cell has the formula:
> =LEFT(A1,5)&"-000"&RIGHT(A1,1)
> then it will display 12345-0001
> just as you want.
> _____________________________________
> Gary's Student
>
>
> "CJ" wrote:
>
> > I need to achieve a result like this: 12345-0001
> > However, the user does not want to type the leading zeros.
> >
> > Help would be much appreciated!!!  Register To Reply

5. ## RE: hyphenated number format with leading zeros to the right of th

Thank you!

"B. R.Ramachandran" wrote:

> Hi,
>
> Enter the following formula in B1, assuming that A1 contains the
> user-entered number.
>
> =LEFT(A1,5)&"-"&RIGHT(10000+RIGHT(A1,LEN(A1)-5),4)
>
> The formula will convert,
>
> 123451 to 12345-0001
> 1234512 to 12345-0012
> 12345123 to 12345-0123
> 123451234 to 12345-1234
>
> If the user enters a number containing less than 6 digits, it would return
> an error as #VALUE!
>
> If you have several user-entered numbers in column A (eg., A1:A101) enter
> the above formula in B1 and fil-in the formula down to B101.
>
> Hope this helps,
> Regards,
> B. R. Ramachandran
>
> "CJ" wrote:
>
> > I need to achieve a result like this: 12345-0001
> > However, the user does not want to type the leading zeros.
> >
> > Help would be much appreciated!!!  Register To Reply