CODEWITHSUNDEEP
arraysexcelexcel-formulaoffice365formula
Nick
Nick

Reputation: 775

Cant Get formula to enter blank cells at end of column

I have data composed on chemicals and hyphened cells in column F of sheet1.

Using the formula in sheet2: =IF('Formulation card'!F7:F60="-","",'Formulation card'!F7:F60)

This works well to convert the hyphened cells to a blank cell if present in F7:F60 of the formula card.

At at end however I get a bunch of '0's entered where Blank non hyphenated cells are present.

I've tried to use an OR Statement as follows:

=IF(OR('Formulation card'!F7:F60="-",""),"",'Formulation card'!F7:F60)

Which to me reads if a - or a "" is present return "". However doing this just returns all columns as Blank. I though I had got my head around using OR statements but apparently not.

If someone could point out whats wrong here that would be great. Let me know if you require more information.

Upvotes: 0

Views: 34

Answers (1)

JvdV
JvdV

Reputation: 75890

Internally all given indices inside an OR() statement will be processed, however only one value of FALSE or TRUE will be returned depending if any of the calculations returned TRUE. Hence why your column will stay "empty". It's actually not empty, but full of zero-width strings.

You can circumvent that behaviour in Microsoft365 using some boolean structure:

=IF(('Formulation card'!F7:F60<>"-")*('Formulation card'!F7:F60<>""),'Formulation card'!F7:F60,"")

Upvotes: 1

Related Questions