Excel CELL Formatting

I want to convert all cells in column A to TEXT
So that in column B I should have only ERRORS (since B = A + 1) (and I cannot add a number to TEXT data like in line 1)

I tried to add an " ' " leading content of cells in A but B still thinks is it a number since the result is not an error

What do I have to format cell in A ?

Thank you
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

what is the purpose here.   the cell in there is already text.  if you put the formula =text(a3)  the result will be true
Saurabh Singh TeotiaCommented:
Use this formula



="'" & a2

Drag this.. It will convert your values to text..


no matter what you do,  whether you add an apostrophe or add with "" or you change format with =text(a2,"@"

in all cases you the + math operation will add the value.

the only workaround i can see here is using the formula like this  =a3&char(160)  then will do the job and you will get Error if you sum anything with this data. see attached file.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gadsadAuthor Commented:
The problem is that importing these cells to an external accounting program all leading 0 are wiped out (00840 becomes 840 for example). SO I have to tell this accounting program that these are Text values in Excel and leading 0 should stay in import process

with using the method of =a3&char(160)  no zero value will be removed.

i simply used it in your file previously attached and you can see leading zeros are still there
gadsadAuthor Commented:
You are right with a3&char(160) and then converting the result of the formula to value (copy + special paste = value) it works and the formula A3+1 return and error. It will try in the accounting software if zero stay while importing

What does the formula  a3&char(160) does exactly ?

Char(160)  in reality is as if you press ALT ENTER
you could also use =a3&Char(10)  it will give the same result
gadsadAuthor Commented:
And what is ALT ENTER in Excel? why it works?
ALT Enter is a line break like other special characters.

open a notepad and then press ALT ENTER and see what happens that is called Alt Enter

why it works, because math operation does not recognise it as number

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gadsadAuthor Commented:
ok thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.