EXCEL HLOOKUP

[Frank Kelly]

Hi all

I have a problem with HLOOKUP. (EXCEL 365)

I need to copy a  column of data from one worksheet to another.

My function works exactly as required EXCEPT:

The source column contains a proportion of empty cells. When these cells are copied Excel puts 0 (zero) into the destination cell. I know about putting a single quote into the null source cell  and this works fine, but the data in the source column is amended frequently by staff who are not computer literate and therefore regularly forget to put in the single quote. I have tried all appropriate cell formats but no joy.

[#2]

Hi Frank,

As variant in Excel Online you may apply custom format like this

And/Or if you have Excel Desktop to edit the file once applying any other custom format like

#,###;-#,###;@

it will be available in Excel Online after that.