Share via

Excel: How can a formula output an empty cell?

Anonymous
2024-03-08T10:27:25+00:00

I am trying to create a column of codes from disparate data in my spreadsheet in Office21.

The two formulae below have been copied down the spreadsheet (ultimately 3,000 lines long) to give these results.

I then want to merge the two columns to create a single text column with these (and six other) codes.

For this block, I have copied then pasted values, then checked with Isblank.

But in all these cells there is still content - for both row 3 cells, =isblank reports FALSE.

I tried to specify a False> NULL() output, but that also left content "#NAME?" in the cells.

Copy>paste>skip blanks works only if the cell is blank.

So it is not possible to use "skip blanks" when I copy the right column onto the left one.

How can these blank cells be made actually blank, so they can be skipped?

Or is there an alternative strategy?

Thank you

=IF(ISNUMBER(SEARCH("hg",BL3)),"HG","") =IF(ISNUMBER(SEARCH("br",BS3)),"BM",)

0
BM
0
BM
HG 0
BM
BM
BM
BM
BM
0
HG 0
0
HG 0
HG 0
0
BM
BM
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

OssieMac 48,001 Reputation points Volunteer Moderator
2024-03-09T00:03:57+00:00

I can only provide you with a work around that removes the formulas from the double quote "blank" cells and you have real blank cells.

Unfortunately, the Replace function does not have a "Find values" option so we cannot simply replace the double quotes values with real blanks. We have to use a workaround.

In the following where I refer to "blank cells", it includes double quote nulls and real blank cells.

  1. Select the entire range containing a combination of non-blank and blank cells.
  2. Select Find & Select -> Find.
  3. Leave "Find what:" field blank.
  4. If Options dialog not displayed, then select Options button.
  5. In the "Look in:" field, select "Values".
  6. Click "Find All" button.
  7. The list of blank cell addresses will be displayed at the bottom of the "Find" dialog (List includes cells containing double quotes and real blank cells).
  8. Use Ctrl and A over the list in the Find dialog to select the displayed cells on the worksheet.
  9. Close the Find dialog (Note: Must do this before next step)
  10. Press the "Delete" key to remove the formulas from the selected cells and leave the selected cells as real blanks.

Now you will have real blank cells. However, the formulas are gone from the blank cells, and I don't know how this will affect your project, but I don't know of any other way of achieving your end result.

I will be interested in your response as to whether this will resolve your issue.

Was this answer helpful?

6 people found this answer helpful.
0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-10T10:48:55+00:00

    I think I found that unless the cells were converted to values i.e. "blank", the formulae were present as content so prevented your method from overwriting with a real blank coding.

    In order to create the coding in a single column, a number of cell contents need to be searched e.g.

    =IF(ISNUMBER(SEARCH("!!!!",$BS4)),"!!!","")

    =IF(ISNUMBER(SEARCH("JRL",$BL5)),"JRL","")

    =IF(ISNUMBER(SEARCH("cvh",$BL6)),"CVH","")

    =IF(ISNUMBER(SEARCH("vic",$BS7)),"NGV","")

    =IF(ISNUMBER(SEARCH("xxxx",$BS8)),"xx","")

    =IF(ISNUMBER(SEARCH("ad",$BL9)),"AD","")

    The row numbers change because that is how they appear in the spreadsheet.

    These are a sample of the various tests used to place the output in a single cell?

    Can one place a single function in a cell to incorporate all these tests with a single relevant output? I have tried making a string of ORs or ANDs but this throws an error.

    This could provide an alternative strategy to copying and special pasting columns.

    I have just discovered "nested" formulae, which seems to have solved this problem with a single formula, 8 tests, 3 lines long.

    Thank you for your patience. Is there a better way?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2024-03-09T10:11:03+00:00

    That is very generous.

    I am preparing the spreadsheet as data input into a website, due at the end of next week, 16th. So there is no immediate rush Any time before Thursday morning UK time would be workable.

    In applying your method, I neglected to say that to make it work, I had to specify paste values (i.e. not All) in the paste special box, or convert the formulae to values as an initial step.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. OssieMac 48,001 Reputation points Volunteer Moderator
    2024-03-08T11:01:25+00:00

    Using IF function and testing for double quotes will return true whether the cell is really blank or contains double quotes as result of formula.

    In the screen shot below, the yellow cells contain real blanks (ie. the formula was deleted) the green cells contain a formula that returns a double quote null value.

    Cell B2 contains formula =IF(A2<>6,"OK","")

    Cell B2 formula was copied down for the length of the data in column A

    The yellow cells had the formula deleted.

    The green cells still contain the formula.

    Cell C2 contains formula =IF(B2="","Blank","Not Blank") and copied down.

    Note that both the yellow cells and green cells are recognised as blank even though the green cells contain a formula.

    Image

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2024-03-08T11:19:32+00:00

    Thank you for the prompt response.

    Taking your example

    a. If you copy the existing col2 onto col3 and you use copy>paste special> skip blanks, does that over write the "Blank" text?

    b. If you convert all col2 to values and you use copy>paste special> skip blanks, does that over write the "Blank" text?

    In my situation in my initial post, in both actions, the "Blank" text (equivalent) is overwriten with an apparently blank cell, but not recognised as such by this command. What are the limitations of copy>paste special> skip blanks?

    Initial state, all cells converted from the formulae

    =IF(ISNUMBER(SEARCH("hg",BL3)),"HG","") =IF(ISNUMBER(SEARCH("br",BS3)),"BM","") to values

    BM
    BM
    HG
    BM
    BM
    BM
    BM
    BM
    HG
    HG
    HG
    BM
    BM

    Copy column 2 over column 1 using copy>paste special> skip blanks,

    BM BM
    BM BM
    BM BM
    BM BM
    BM BM
    BM BM
    BM BM
    BM BM
    BM BM

    Was this answer helpful?

    0 comments