apostrophe - what it means for excel

[lezawang]

Hi
Why when i type ' in a cell, then excel hides it, it only show in formula bar?
i know if i do '100 then excel will take this as a text and if i want to show a formula then i type '=sum(1,1)

is that what apostrophe used for?

Thanks alot.

 

[shaowu459]

Yes, add apostrophe before anything you entered, it will be recognised as text for Excel. However, there are more use of apostrophe, for example, you have a worksheet named "9 clock", in another worksheet, you simply enter = and select a cell in 9 clock, you will get ='9 clok'!H17, there will be apostrophe around the sheet name.

 

[lezawang]

I want to add something I just found, if a cell has ' then
countblank() will count it as a blank cell and
countif(range,"*") will count it as a cell which has text

do not see contradiction here?

 

[iggy_]

Having just an apostrophe must fall under their definition of formulas that result in blank text. If you check out the info for the function on their website (link below) they state that a formula that returns blank text will also be counted. For example ="" would also result in a result of 1 in a countblank() function.
Countif in your example will capture any cell with text because star (*) character is just a wild.

https://support.office.com/en-us/article/countblank-function-6a92d772-675c-4bee-b346-24af6bd3ac22