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 '15 at 12:00
  • No need to keep original values. Just convert the string to an X. – nfrost21 Jan 7 '15 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 '15 at 12:30

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.
| improve this answer | |
  • 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 '15 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.

| improve this answer | |

You must log in to answer this question.

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