concatenate locale bug?
 

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 locale bug?
 

< 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

concatenate locale bug?
 

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

concatenate locale bug?
 

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

concatenate locale bug?
 

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

concatenate locale bug?
 

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

concatenate locale bug?
 

That requires enabling macros.

Our sites have security policies.

Any other suggestions?

Thanks

concatenate locale bug?
 

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

concatenate locale bug?
 

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