concatenate locale bug?

  • Thread starter Thread starter cagdas.ozgenc
  • Start date Start date
C

cagdas.ozgenc

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?
 
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=author:keepitcool+gluetext

hth


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


(e-mail address removed) wrote in
 
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
 
Back
Top