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
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
what is the purpose here. the cell in there is already text. if you put the formula =text(a3) the result will be true
Use this formula
=text(a2,"@")
or
="'" & a2
Drag this.. It will convert your values to text..
Saurabh...
=text(a2,"@")
or
="'" & a2
Drag this.. It will convert your values to text..
Saurabh...
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
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
ASKER
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
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
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
ASKER
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
.
What does the formula a3&char(160) does exactly ?
Thanks
.
Char(160) in reality is as if you press ALT ENTER
you could also use =a3&Char(10) it will give the same result
you could also use =a3&Char(10) it will give the same result
ASKER
And what is ALT ENTER in Excel? why it works?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok thank you