Excel deletes the ‘0’ at the start of a number?

_Jimlad_ · 5 Dec 2006 at 13:23

Is there any way of turning this feature off? As I can’t see one in the options, I know you can format the text, but this data is coming in from a SQL report and ideally I would like to turn it off in Excel (2003) first if possible.
as it is a daily thing.

Cheers Jim

gjrc · 5 Dec 2006 at 13:30

can i ask why you need the 0 if there is no decimal point after it?

Origin · 5 Dec 2006 at 13:45

iirc you need to format the cells as text before the data is copied into it.

Gibbs · 5 Dec 2006 at 13:51

Origin said:
iirc you need to format the cells as text before the data is copied into it.

works a treat

Tute · 5 Dec 2006 at 13:56

gjrc said:
can i ask why you need the 0 if there is no decimal point after it?

Phone number maybe?

_Jimlad_ · 5 Dec 2006 at 14:24

it's an SQL report that opens in excel, so i don't get the chance to change the format of the cell. which i'm aware you can do, i just wanted to know if you can turn the option of the disappearing 0 in excel off.

WIBSBOT · 5 Dec 2006 at 15:23

Only way I can think of is to have a macro format the cells before you import the SQL report.

Apparently, the only other way you can do it, is to append ' (Apostophe) to the beginning of the data, which then forces excel to treat as text format.

http://www.rpmsoftware.com/wiki/index.php/Leading_zeros_in_Excel

s-p · 5 Dec 2006 at 15:36

You can keep the 0 at the front of numbers by placing a ' (single quote) infront of the text. This effectively emulates the process of changing the formatting of the cell from number to text. But that's no better than going in and formatting the cell in the macro.

I don't believe there is a way of turning this feature off unfortunately.

Too slow

_Jimlad_ · 5 Dec 2006 at 16:16

yeah due to the vast amount of data coming in from this report, that's not an option really, it's a bit of a bugger... but nevermind

thanks for all the help,.

MetA · 5 Dec 2006 at 17:02

Easiest (!) is to have the SQL report precede with a ' .

Or run it through a process to add it after.
What are you running all this on ? Un*x or Windoze ?

_Jimlad_ · 5 Dec 2006 at 18:08

MetA said:
Easiest (!) is to have the SQL report precede with a ' .

Or run it through a process to add it after.
What are you running all this on ? Un*x or Windoze ?

the report is coming out of this eductational database called SIMS and the report comes out in excel on windows xp.

cheers Jim