Ask Your Question

format number as currency based on next cell content

asked 2017-12-25 09:59:30 +0200

Mikele gravatar image

updated 2017-12-25 10:00:07 +0200


I have a table with two columns: Column A = numbers Column B = currency codes (i.e. USD, JPY, EUR, etc...)

example: ^ (screenshot)

how can I have cells on column A to automatically format as the correct currency based on the code column B on the same row?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2017-12-27 08:58:04 +0200

Mikele gravatar image

updated 2017-12-27 09:00:01 +0200

Meanwhile, it seems like I have succeeded in using conditional formatting, like this:

(to see the conditions applied to the data in your screenshot, replace Q52 and R52 with A2:A500 and B2 respectively ... I had to create a custom style for each currency I am testing against)

It took me a long while to figure it out, because I was missing to first clear all existing formatting for the cells in column Q (column with numbers in my table)

The table I am building is a work-in-progress, I haven't had the chance to 100% verify this approach, but at first glance it seems to work.

edit flag offensive delete link more


This is a good solution as well. I upvoted so that you have 20 karma points, which is enough to accept your own answer if that's what you decide.

Jim K gravatar imageJim K ( 2017-12-27 12:09:54 +0200 )edit

answered 2017-12-27 06:50:26 +0200

Jim K gravatar image

updated 2017-12-27 06:51:20 +0200

Copy format codes from Format -> Cells, Number, Currency into a lookup table.

D         E
~~~~~~~~  ~~~~~~
currency  format
USD       [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00
EUR       #,##0.00 [$€-612];[RED]-#,##0.00 [$€-612]

Now use the TEXT function and look up the format. Enter the following formula for A2.


The result:

formatted currency values

edit flag offensive delete link more


Thank you Jim, very interesting, I didn't know about the TEXT function; I'll give it a try!

Mikele gravatar imageMikele ( 2017-12-27 08:59:43 +0200 )edit

answered 2020-03-06 09:13:15 +0200

bijalvp gravatar image

this is what I did and it's working for me:

  1. created two styles namely USD and INR
  2. created a conditional format for the range I needed with 2 conditions: 2.1 condition 1 was Formula Is > referenced the cell here with the value it needed to look (=) for and applied the appropriate Style 2.2 condition 2 was Formula Is > referenced the same cell as above with any other value (>) other than the one above and applied the appropriate Style

image description

So now I just apply this conditional format to all cells that contain currency values and based on the C6 value in sheet $Variables the correct currency symbols are displayed.

hope this helps

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2017-12-25 09:59:30 +0200

Seen: 531 times

Last updated: Mar 06