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

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

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

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

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