why excel ignores ( ' ) at the start of a string

[nambaliya]

Hello Everyone,

I am new to this forum. I have a query. When I insert a value in a cell which starts with ( ' ) excel simply ignores it. Why?

example: A1 = 'test' then I can see ( ' ) in the formula bar but when I say B1 = left(A1,1) it returns 't' as an answer and I was expecting ( ' ). Why?

How do I make excel not to ignore ( ' ) ?

problem.jpg

Thanks,
Nirav

[vlady]

use two single quote ' '

''test'

then
=left(A1,1)

[nambaliya]

Thanks for your prompt reply vlady. But it would be good to know that why does excel do it?

But I am reading some parameters from simulink model to excel sheet which comes in the format 'name'. After editing a few parameters when I write them back ( ' ) is being missed. If I dont edit any param then it is fine.

Please refer to the image attached. There column A contains unchanged parameters, B contains changed parameters. As we can see C returns ( ' ) when typed = left(A2,1) whereas D clearly missed ( ' ).

Many Thanks,
Nirav

[nambaliya]

problem_2.png

Heres the missing image.

[vlady]

The single quote at the start of a cell is used to force text format so you cannot extract that

to enable you to have quote is to type two quote that is first quote is to "force" the value to become a text and then second quote display a quote along with that string.


when you edit them be sure to put two single quotes along..

[nambaliya]

Thanks for making it clear vlady.

Is there any way we can disable that forcing text format thing?

Thanks,
Nirav

[vlady]

i think no
if that is not included the value become numerical in nature
so if you have a value Hello! with out this in excel that would be numbers
its the way of excel

this is also found on different kinds of data i.e. alphanumeric/symbols etc.