MacMost Q&A Forum • View All Forum QuestionsAsk a Question

Is It Even Possible To Combine String and Currency Values In One Cell?

I have a spreadsheet where I use the formula IF(BA21,”x”,”£0.00″). Basically, if the checkbox in BA21 is ticked, I get an x in the cell, otherwise I want to get a currency value of £0.00. I want to be able to replace that currency manually with e.g. £5.20 if I need to.

The problem here is I’m dealing with two different types of format in the same cell – text and currency. When I enter the currency value e.g. £5.20, the formatting then defaults to numeric then I get £5.

I’ve racked my brains to find another way to do this, but so far, nothing!

Do you have any suggestions as to how I can make this work in a tidier manner?

I run a lottery syndicate and this spreadsheet is a record of members contributions and weekly winnings. I have a “Week in play” field with a tick box. If that is ticked, then subsequent weeks show as blank by using the x or £0.00 value. If the tick box is unticked, what’s won that week is entered instead of the formula. It looks tidy, but the underlying code is messy.
Jock Wilson

Comments: 2 Responses to “Is It Even Possible To Combine String and Currency Values In One Cell?”

    2 years ago

    Just use a custom format for the cell. So the formula would be:

    Then select the cell and go to the Format sidebar, and the Cell tab at the top. Select the Data Format "Create Custom Format..."

    See for a lesson on how to use custom formats.

    The format you want is the #,### followed by the .##
    Then click on the .## and turn on Show Trailing Zeros.
    Then add £ at the beginning so the result is £ #,### .00

    Then add a rule. Set that to "if equal to" -1 then the format is just "x."

    Now when the checkbox is checked, the value is -1 and you get an "x" in the cell. Otherwise, you get values shown as £5.20.

    Jock Wilson
    2 years ago

    Thank you very much - this does exactly what I needed - I've been looking for a solution to this for at least two years now! Absolutely superb service!

Comments Closed.