Hi,
I feel dumb having to ask this, but it's driving me up the wall...simply 'cos I am not getting this silly thing right...HELP!!
Look at the attached picture...
How do I display the value with a leading 0. Even converting to text does not help???
Add leading 0
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Add leading 0
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78636
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Add leading 0
Can't do Hans...
The idea is to paste the formula as values and then use Text To Columns to convert it into a valid date. I have done this dozens of times, but NEVER had the problem with the leading zero before???
The idea is to paste the formula as values and then use Text To Columns to convert it into a valid date. I have done this dozens of times, but NEVER had the problem with the leading zero before???
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78636
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add leading 0
The length of a string is a number, not a string, so your formula should have been
=IF(LEN(B2)=7,"0"&B2,B2)
Another option would have been
=RIGHT("0"&B2,8)
=IF(LEN(B2)=7,"0"&B2,B2)
Another option would have been
=RIGHT("0"&B2,8)
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Add leading 0
Perfect....
My mistake with the quotes around the 7.
(Silly me!!!)
TX
My mistake with the quotes around the 7.
(Silly me!!!)
TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.