I have a spreadsheet which contains a mixture of text strings and empty cells. I'm looking for a formula to replace the value of all non-empty cells with a single character 'x', while leaving empty cells blank.

  • Please specify if you want to keep the original values.
    – dan
    Jan 7, 2015 at 12:00
  • No need to keep original values. Just convert the string to an X.
    – nfrost21
    Jan 7, 2015 at 12:03
  • Please, specify this point within your original question (OQ) and not within comments. Comments are destined to be destroyed.
    – dan
    Jan 7, 2015 at 12:30

2 Answers 2


Here are a few hints:

  • use an auxiliary column;
  • use functions ISBLANK and IF;
  • replicate the auxiliary column within the original one;
  • remove the auxiliary column.
  • Thanks for the tips Daniel. I'd prefer not to use an auxiliary column if possible as I have around 20 columns that I need to apply the formula to.
    – nfrost21
    Jan 7, 2015 at 12:31

Using Daniel's tips I've come up with this solution:

  1. Create an auxiliary column
  2. Add the formula:

    IF(NOT(ISBLANK(A1)),"x"," ")

  3. Copy formula to all rows.

  4. Overwrite original column with values from auxiliary column.

This isn't an ideal solution as I need to apply it individually to around 20 different columns. If anyone knows a way to do this without using an auxiliary column I'd be interested to hear it.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .