Try this. Worked for me.
=IF(ISBLANK(F6), "", CONCATENATE("Check rule ", F6))
Fffffuuuu....
So I click on the cell F6, clear contents, and it works. There are invisible characters in the cells.
AARGH
EDIT and if I use TRIM() it stops working altogether, even if I also clear contents.
Probably a zero in the cell and excel is set to not display zero values.
Not the same as blank.
Nope.
Pilot error then. 😉
try CLEAN instead of TRIM
I'd have approached it from the other end TBH
It's always easier to make a formula conditional on a positive result e.g =if (f6>0..... than a negative result e.g =IFBlank(f6....
I'd have done the formula like this. (assumes a numerical value in f6)
=if(f6>0,CONCATENATE("check Rule ",f6),"")
This way it'll always default to blank unless the positive condition is fulfilled.
Edit, this will work for text values of f6 also but will catch non displaying zeros
[quote="miketually"]Some really big financial decisions were found to be being made using 'data' from really shonky spreadsheets. It's scary.
Are you looking at me ? 😀
I can confirm that "scary" is quite an understatement... I'm looking at things in the £billions here.
Thanks.
Gave up in the end - much as I want to find out what's going on, I have code to write. Cleared out the cells by hand.
[quote="Rob Hilton"]...whereas *way* too many people use it for absolutely everything.
Using Excel as a word processor.
Using Word as a spreadsheet.
Once, when I had asked for detail of some hours from timesheets, the person took screengrabs from Excel and pasted them into Word as images, then sent me that. WTF ???
