Quirky formatting question

[normpam]

Hi everyone.
If I type in 0712 into a cell, I get 712
If I format the cell first for 'Text', I get 0712
If I then edit the cell, and put an apostrophe before the 0712, the formula bar will show the apostrophe, but the cell contents will only show 0712.

If I use a VBA code like this:

For Each Cell In Range("C4:C8")
Cell.Value = "'" & Cell.Value

it works just the same as if editing the cell.... BUT

If I use the a formula ="'" &c4, putting an apostrophe before the cell contents, then Excel shows '0712 in the cell. Why does Excel show the apostrophe when a formula is used, but if the cell is manually edited, or the apostrophe is added using VBA, it does not show up in the cell?

 

[cerfani]

probably because you are concatenating which results in a string and it does not require the control character to tell Excel it is a string ... just do this... =""&c2

 

[normpam]

probably because you are concatenating which results in a string and it does not require the control character to tell Excel it is a string ... just do this... =""&c2

I tried that, but then when I convert the formula to a value, I still get 0712, not '0712.

 

[philwojo]

My understanding is that if you use the single quote, or apostrophe, it is telling Excel to treat anything that follows it as text.

So if you would want to see that you can do two (2) single quotes before your number, it will then display as '0712.

 

[normpam]

My understanding is that if you use the single quote, or apostrophe, it is telling Excel to treat anything that follows it as text.

So if you would want to see that you can do two (2) single quotes before your number, it will then display as '0712.

Actually, what I would like it to do is show just 0712 in the cell, but '0712 in the formula bar. So, if I just do it manually, or do it with VBA, it will work, but if I use a formula ="'" (quotation marks with an apostrophe in between), the apostrophe shows up in the cell itself, not just in the formula bar....

 

[cerfani]

To elaborate on what Phil said, in excel, ' is a control character, in order to have it print in the text you need to precede it with the escape character which happens to also be '

="''"&C2

so inside the quotes... "" is the escape character ' and then the control character it is escaping '

google what a control character and an escape character is

To print a control character you need to precede it with the escape character.