Greetings.

Concatenate function seems to have a bug in Excel 2003 when same sheet
is displayed on a different locale.

For example two cells contain
A1 A2
Hello 10.00%


CONCATENATE(A1,";",A2)

yields

Hello;0.1 (with dot)

and

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0.1 (with dot)

When I send this sheet to Russia where decimal separator is comma not
dot

CONCATENATE(A1,";",A2)

still yields

Hello;0.1 (with dot)

but

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0,1 (with comma)

And there are VLOOKUPs in the sheet, and they all fail because of this,
since CONCATENATE is used to create compound-key field for VLOOKUP.

Any suggestions?

Thanks

< CONCATENATE(A1,";",A2)
still yields
Hello;0.1 (with dot)


Even after recalculation?


--
Kind regards,

Niek Otten


wrote in message
oups.com...
Greetings.

Concatenate function seems to have a bug in Excel 2003 when same sheet
is displayed on a different locale.

For example two cells contain
A1 A2
Hello 10.00%


CONCATENATE(A1,";",A2)

yields

Hello;0.1 (with dot)

and

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0.1 (with dot)

When I send this sheet to Russia where decimal separator is comma not
dot

CONCATENATE(A1,";",A2)

still yields

Hello;0.1 (with dot)

but

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0,1 (with comma)

And there are VLOOKUPs in the sheet, and they all fail because of this,
since CONCATENATE is used to create compound-key field for VLOOKUP.

Any suggestions?

Thanks

I've tested it with xl97 NL version and YES I see what you mean.
Problems do not occur in xl2003 UK.

So it appears that it's more a matter of Excel Language version
than of ControlPanel locale.

You could either
force numeric fields with N() function.

write a small udf to replace Concatenate.
(for example see
http://groups.google.com/groups?q=au...tcool+gluetext

hth


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote in
groups.com

Greetings.



And there are VLOOKUPs in the sheet, and they all fail because of
this, since CONCATENATE is used to create compound-key field for
VLOOKUP.

Any suggestions?

Thanks

perhaps do
CONCATENATE(A1,";",Text(FLOOR(A2,0.1),"0\.0"))

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Greetings.

Concatenate function seems to have a bug in Excel 2003 when same sheet
is displayed on a different locale.

For example two cells contain
A1 A2
Hello 10.00%


CONCATENATE(A1,";",A2)

yields

Hello;0.1 (with dot)

and

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0.1 (with dot)

When I send this sheet to Russia where decimal separator is comma not
dot

CONCATENATE(A1,";",A2)

still yields

Hello;0.1 (with dot)

but

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0,1 (with comma)

And there are VLOOKUPs in the sheet, and they all fail because of this,
since CONCATENATE is used to create compound-key field for VLOOKUP.

Any suggestions?

Thanks

I think the problem is of a recalculation issue. Excel caches these
values incorrectly. They need to be automatically updated when file is
opened in a different locale.
What can I do? I don't want to keep reminding people to recalculate my
excel sheets.

Thanks

Include an Auto_open Sub that issues a Calculate or maybe even a
CalculateFull.

--
Kind regards,

Niek Otten

wrote in message
ups.com...
I think the problem is of a recalculation issue. Excel caches these
values incorrectly. They need to be automatically updated when file is
opened in a different locale.
What can I do? I don't want to keep reminding people to recalculate my
excel sheets.

Thanks

That requires enabling macros.

Our sites have security policies.

Any other suggestions?

Thanks

Have you tried "&"

=A1&";"&A2

I'm not optimistic this would make any difference but might be worth a try.

Regards,
Peter T

wrote in message
ups.com...
That requires enabling macros.

Our sites have security policies.

Any other suggestions?

Thanks

Maybe:

=YourFormula&TEXT(NOW()*0,"#")

Let us know if it works

--
Kind regards,

Niek Otten

wrote in message
ups.com...
That requires enabling macros.

Our sites have security policies.

Any other suggestions?

Thanks