We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

format number as currency based on next cell content

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

Mikele gravatar image

updated 2020-09-21 23:16:26 +0200

Alex Kemp gravatar image


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

example: https://drive.google.com/open?id=1tvm... ^ (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: 791 times

Last updated: Mar 06 '20