€ Euro symbol in referenced cells...

[RobbieC]

Hi there, I have a huge speadsheet, originally written with £'s in mind

I have been given the task of turning it into a multi-currency sheet. It has literally hundreds of cells with formulas such as:

=CONCATENATE(A108," £",I108)

where the £ sign is integrated into the formula...

I have made cell A1 my 'variable cell' which I can change to the correct currency as required, for example A1 = $, there fore the new formula is:

=CONCATENATE(A108," ",A1,I108)

and so on...

This works fine for all currencies apart from the Euro sign € the result just comes out as |

I've tried formatting the cell A1 as text, tried chr(128), but nothing is changing the | to a €

What do I do? If you can point me in the right direction, I'd be eternally gratefull

Thanks

 

[mole999]

if the euro will always be a euro, custom format the cell then you only need to add a value and not the sign, or format as that specific currency which does the same/similar

 

[RobbieC]

The problem is that the currency sign is embedded into the formulas (and there are literally hundreds of them). That's why I thought I could reference a cell with the relevant symbol

However, even if I do a simple formula such as

=A1

the result always shows a |

all other currency signs work fine: £ $ ¥ etc... only the € won't show properly

Many of the cells contain formulas such as

=IF(I85<>0,CONCATENATE(G84," x ",A85,H85," £",I85),"")

which dynamically generates:

5 x flush covers (provided by client) @ £4 each = £20

so it's all contained within the same formula ready for output. Re-writing this entire sheet would be an absolute nightmare. I thought that referencing the currency symbol would be the simplest method.

=IF(I85<>0,CONCATENATE(G84," x ",A85,H85," ",[B]A1[/B],I85),"")

Custom formatting the cells to whichever currency will mean duplicating the sheet for each currency plus a re-write of every cell containing the number...

Why will Excel not allow me to reference the Euro sign directly from the source cell A1? Frustrating...

 

[thisoldman]

What font are you using in the worksheet?

Can you isolate the pipe character, the "|", and tell us what =UNICHAR("|") returns?