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!!!
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!!!
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!!!
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!!!
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!!!
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!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks