Prevent Leading Zeros in Excel

[grnzbra]

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.

 

[strongm]

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

.###

 

[mikrom]

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

 

[Andrzejek]

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

 

[grnzbra]

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.

 

[Andrzejek]

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

---- Andy

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[strongm]

>CHANGE the NumberFormat to TEXT

You'd think, yes, but

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

 

[Andrzejek]

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:
[pre]
1234
7.25
.123[red]
XYZX[/red]
10.2
[/pre]

---- Andy

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

 

[SkipVought]

@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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!