Hi, Im trying to insert 00 to the start of a number so that 98765 becomes
0098765, Ive set up a concatenate but for some reason this only works in the
first cell

=CONCATENATE($E$1,C2) where e1 contains 00.

When I copy this down the sheet I only get the number in the first column,
however when I then go and double click in the cell the formula updates and
gives me the correct answer

Any ideas?

Thanks

Hi,

I suggest formatting the cell. Format - Cells - Number - Customr. In
the text box change the format to 0000000 (to indicate 7 digits).

Regards,
A

"jeanette.rimmer" wrote:

Hi, Im trying to insert 00 to the start of a number so that 98765 becomes
0098765, Ive set up a concatenate but for some reason this only works in the
first cell

=CONCATENATE($E$1,C2) where e1 contains 00.

When I copy this down the sheet I only get the number in the first column,
however when I then go and double click in the cell the formula updates and
gives me the correct answer

Any ideas?

Thanks

If the length of the entries must be 7...

=TEXT(C2,"0000000")

will do so.

BTW, Calculation must be set to Automatic (see Tools|Options).

jeanette.rimmer wrote:
Hi, Im trying to insert 00 to the start of a number so that 98765 becomes
0098765, Ive set up a concatenate but for some reason this only works in the
first cell

=CONCATENATE($E$1,C2) where e1 contains 00.

When I copy this down the sheet I only get the number in the first column,
however when I then go and double click in the cell the formula updates and
gives me the correct answer

Any ideas?

Thanks



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

hi, thanks

The calculation was set to manual, thanks for that

My husband now thinks Im an excel guru!

cheers
"Aladin Akyurek" wrote in message
...
If the length of the entries must be 7...

=TEXT(C2,"0000000")

will do so.

BTW, Calculation must be set to Automatic (see Tools|Options).

jeanette.rimmer wrote:
Hi, Im trying to insert 00 to the start of a number so that 98765 becomes
0098765, Ive set up a concatenate but for some reason this only works in
the first cell

=CONCATENATE($E$1,C2) where e1 contains 00.

When I copy this down the sheet I only get the number in the first
column, however when I then go and double click in the cell the formula
updates and gives me the correct answer

Any ideas?

Thanks



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.