15

I'm trying to create a custom cell format that looks like a ratio. I.E.

24 : 1

In the custom formatting option on the cell when I try to put the following in I get an error saying it's not a valid format

# : 1

I believe this is because of how I want to use the ":" character and it's normal use with time formatting.

Is there a way to escape special characters like ":" so they appear as just text?

Thanks

Paul

11

You can use the backslash (\) to escape characters like the colon.

In your example, then, you can use the format # \: 1.

Oddly, a format like # \: # splits up a number to display a number of digits to the right of the colon equal to the number of hash marks to the right, with the rest of the digits to the left. Some examples with the number 44.5:

# \: #   displays '44 : 5'
# \: ##  displays  '4 : 45'

If the number were 445 instead of 44.5, you would get exactly the same results. It's also possible to substitute question marks (?) for the #'s and get the same outcome.

10

The shorthand way of using @tjd802's solution is to add a ' to the front of any data you put in a cell.

For instance:

24 : 1

Should be inputted as:

'24 : 1

It will escape the content as text and ignore errors

1
  • This is a helpful answer if you want to prevent the cell to convert to a formula, however chuff's answer is the correct answer because the question was about cell formatting.
    – Innovaat
    Jan 17 '18 at 13:47
0

I'm not sure if this will work in general, but for the example format, you can use the following instead.

# : "1"

From playing around with this format, it seems the colon is not so much the issue as what follows the colon is. Text is okay, but numbers are not, apparently.

0

If I understand your question correctly, all you need to do is set the cell to "text" before entering your data, and then ignore any errors. Like this:

enter image description here

EDIT: I fat fingered the demo data. It does work with a colon as well as a period.

0

You can also use CHAR() to insert a special character, instead of trying to escape it.

=CHAR(58)

Results in :

For OP's question, you could do a CONCAT:

=CONCAT(24,CHAR(58),1)

Which shows as

24:1

Some common CHAR codes:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.