Excel CELL Formatting

Hello
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
TEST.xlsx
gadsadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
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

=text(a2,"@")

or

="'" & a2

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

Saurabh...
ProfessorJimJamMicrosoft Excel ExpertCommented:
gadsad

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.
TEST.xlsx
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
ProfessorJimJamMicrosoft Excel ExpertCommented:
gadsad,

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 ?

Thanks
.
ProfessorJimJamMicrosoft Excel ExpertCommented:
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?
ProfessorJimJamMicrosoft Excel ExpertCommented:
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.