Need to show Currency

[hsmwill3]

I have a large spreadsheet which I export from accounting software with multiple currencies.
The currencies show as follows:

€20,502.75
£886.49
$6,460.00

Obviously there are thousands more lines.

I need a formula which will convert these all into GBP.

Currently I am manually typing USD, GBP or EUR next to the line and then using =IF(J2="GBP","1","0"), =IF(J3="EUR","0.84","0"), =IF(J4="USD","0.77","0")
I then sum these cells and multiply the value so it comes back as GBP.

This is very long winded.

If anyone knows a formula which can take me straight from $500 to the GBP value for mutli currencies that would be great.
If not, then just a formula from £500 to show GBP in the next cell.

Thanks in advance,
Matt

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[hsmwill3]

Thanks I have attached now

[torachan]

surely your formula are wrong
you have added a factor to sum
should this not be a conversion factor (i.e. multiply or divide)

[Pepe Le Mokko]

The easiest way would be to build a small table with currencies in first column and exchange rates in the second and use a VLOOKUP or INDEX/MATCH to retrieve the rates.
(BTW in a formula like =IF(J2="GBP","1","0") numbers between double quotes are considered text which might not be what you want)

[hsmwill3]

thank you for spotting this haha! Been sending some pretty off figures for a month now

[hsmwill3]

Hi,

The currency conversion I get, but the main part which takes so much time is manually entering in GBP or EUR or USD for each line. Is there a way to do this automaticcaly if it shows £300 or $400 etc ?

[Pepe Le Mokko]

Is the string £300 entered manually or using formatting ?

[hsmwill3]

Hi, sorry for the delay.
The currency is exported from accounting software so I assume it is inputted manually.

[Pepe Le Mokko]

The currency seems to be the result of formatting, so I extracted it with an old in-built macro (GET.CELL) to build table and results.
See the Name Manager for the reference of the Curr function. This function can now be used anywhere in your sheet