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

C

CJ

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

Gary''s Student

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
 
B

B. R.Ramachandran

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
 
C

CJ

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.
 
C

CJ

Thank you!

B. R.Ramachandran said:
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
Click to expand...
 
Top