1

This keeps bugging me, I've checked various Excel forums but it seems this is either a trivial problem (an I'm an idiot) or this simply wasn't asked before:

Let's say I have two columns with data column A contains ISO currency codes, like EUR, GBP, USD, etc. The other column - B, contains amounts.

The expected outcome is to have a third column C, where i'd have data from columns A and B joined in a way that a custom number format is created (currency type) where currency code is not selected from the drop-down list in the formatting menu but taken from cells in column A. It should look like this:

Sample, output should be a number, not text

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
Inquisitive_Torquemada
  • 11
  • 1
  • I think your options will be to use either Conditional Formatting; or VBA with event triggered code. There is no built-in facility that will preserve the value as a number. – Ron Rosenfeld Aug 27 '20 at 00:06

2 Answers2

1

Good Day,

Assuming you only have 3 currencies you could use a conditional formatting to add the current ahead of the number without converting it to text. Please see the screen cap below. Notice it will still do math on these cells (the total in the screen cap. enter image description here

Its odd Excel shows the number component of the format as 38718 when it is #####

enter image description here

I hope this helps, Brad

BradR
  • 615
  • 4
  • 11
  • Hi Brad, thank you for your input! Fact is - I got 46 different currencies here... Isn't there any way to tell Excel to get the "GBP" or "EUR" from a cell with specific adress rather than set it manually via custom format? Granted, it does work for small number of currencies and I can add the amounts with this formatting but it's tedious for 46 different currency codes and I would rather Excel recognized currency dynamically (by taking currency code from cells). Thanks again though! – Inquisitive_Torquemada Aug 18 '20 at 18:39
0

You can use =CONCATENATE(B2;" ";A2) or the shorter equivalent =B2 & " " & A2 for the first row. Then select the cell and copy down.

Hannu
  • 8,740
  • 3
  • 21
  • 39