[Excel] Converting float and dealing with leading zeros

So, for the Nth time I've received an Excel file that has a field which should have leading zeros. And instead, I've received a file that uses the PESEL (Polish Social Security Number) formatting to make it LOOK like the data is correct. Which SQL Server promptly ignores. So you can fix the Excel column ( =text(B1,"00000000000")

If you try a straight up conversion, it fails.
Example:
Should be 00123456789
Looks like 00123456789
Actually is 123456789
"select convert(varchar,crap_field) from your_table" yields: 1.23456e+009
ALTER TABLE fails because the value is too large (numeric overflow).

Fix: double convert, then add leading zeros.

select convert(decimal(11,0),crap_field) from your_table --getting there
select convert(varchar(11),convert(decimal(11,0),crap_field)) --now it's a varchar
select right('00000000000' + --11 zeros
  convert(varchar(11),convert(decimal(11,0),crap_field))
  , 11) --add leading zeros. Done!

Note the table is the same, and you can't just UPDATE the table - the format is still wrong.

[WAT] Weird conversion error in SQL Server

Here's a fun conversion that will bite you.

DECLARE @test VARCHAR(10)
SET @test = '50000'
SELECT convert(varchar(5), convert(int, @test))


You get back "50000".
Now do this:

DECLARE @test VARCHAR(10)
SET @test = '500000'
SELECT convert(varchar(5), convert(int, @test))

What do you get back? "*". Nifty.