×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Prevent Leading Zeros in Excel

Prevent Leading Zeros in Excel

Prevent Leading Zeros in Excel

(OP)
If someone enters .123 into a numeric column (or a General column), Excel will add a leading zero resulting in 0.123.
Is there any way, other than defining the column as Text, to prevent the leading zero from being added? This needs to be done for a particular workbook, not the Excel app in genera.

RE: Prevent Leading Zeros in Excel

Yes, appply a custom format, simplest of which might be

.###

RE: Prevent Leading Zeros in Excel

Right click on the number 0.123, select Format Cells..., then select from Category: the option Custom, enter into the field Type: your own format .000 and click at OK button. This changes the format of the number 0.123 to .123

RE: Prevent Leading Zeros in Excel

I don't know if other countries use this format of displaying numbers as .123, but - as you can see - Excel (by default) displays that as 0.123 and I would not try to change it.
US is the only country (as far as I know, I could be wrong) that omits 0 as a whole number when decimals are presented. In my opinion, it is easy to not notice a period in .123 and take it as one hundred twenty three, which could be an issue.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Prevent Leading Zeros in Excel

(OP)
The reason for this is that the spreadsheet is being imported into SQL server and some fields have 4 character limits - the leading zero makes it 5.
Thank you all for the assistance.

RE: Prevent Leading Zeros in Excel

So, your numbers from Excel end up in SQL server as text?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Prevent Leading Zeros in Excel

"So, your numbers from Excel end up in SQL server as text?"

Before ANYTHING has been entered into this 5-byte limit column...
SELECT the column and CHANGE the NumberFormat to TEXT.

This will leave any numeric quantity entered into this column exactly as you enter it without any changes.

FAQ68-7375: Be Aware: Excel can Change Your Data

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Prevent Leading Zeros in Excel

>CHANGE the NumberFormat to TEXT

You'd think, yes, but

Quote (grnzbra)

other than defining the column as Text

and yes, yes, I know that applying custom formats to a numeric field is for Excel display purposes only, and that therefore the SQL import routine is going to have to do some extra work - but the OP seems insistent. Perhaps there are some other constraints or requirements that have not been shared with us that make TEXT a no-no ...

RE: Prevent Leading Zeros in Excel

Hopefully this numeric field from Excel saved as text in SQL server is not used in any calculations later on, because it would be kind of difficult to calculate anything using data like this:
1234
7.25
.123
XYZX
10.2
 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Prevent Leading Zeros in Excel

@grnzbra,

You never responded to strongm's suggestion of applying a .#### NumberFormat to the column. Then I suggested changing the NumberFormat to TEXT, before strongm pointed out that you explicitly excluded applying a NumberFormat of TEXT to this column.

And really, with a 5-byte numeric limit, I'd write a procedure to run in the Worksheet_Change event to impose these restrictions.

But we're all waiting for your reply!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close