concatenate

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" <akyurek@xs4all.nl> wrote in message
news:42e4afda$0$11073$e4fe514c@news.xs4all.nl...
> 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, 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" <akyurek@xs4all.nl> wrote in message
news:42e4afda$0$11073$e4fe514c@news.xs4all.nl...
> 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.