# How to convert number to text in excel

on
Need to convert a whole column, and with a '0' in front.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

try

=text(A1,"00000")

Adjust the number of zeros to how many digits you want to show.

cheers, teylyn
Most Valuable Expert 2011
Awarded 2010
Commented:
You need to copy the formula down, of course.

If you just want to prepend a zero to numbers with varying numbers of digits, like

1
11
111

becomes

01
011
0111

then you can use

="0"&text(A1,"0")

and copy down.

cheers, teylyn
Commented:
from microsoft, for how to convert no to text
http://support.microsoft.com/kb/213360

Commented:
if you want the number as-is without imposing a format then simply use

=""&A1

Saqib

Commented:
sorry

="0"&A1

Commented:
I tried ="0"&text(A1,"0"), but not work, the 2nd "0" seems not correct.
Most Valuable Expert 2011
Awarded 2010

Commented:
can you post a few samples of your numbers? An Excel File would be best.
Most Valuable Expert 2011
Awarded 2010

Commented:

See attached for a file with numbers, converted with the formula

="0"&text(A1,"0")

Book4.xls
Sr. Consultant

Commented:
turbot_yu,

check the attached sheet, if it works, hide the column B.

Q-26929132.xls
Most Valuable Expert 2011
Awarded 2010
Commented:
amit, interesting approach. This can be done without a helper column, though:

=TEXT(A1,REPT("0",LEN(MAX(\$A:\$A))))

cheers, teylyn
Commented:
I thought that the problem was with non integer numbers then something like this would be required.

="0" & TEXT(A1,"0.??????????????")

Commented:
Is that an improvement on

="0"&A1

Commented:
turbot_yu,
If you have to accept so many answers then why leave out mine?

teylin
The reopening does not seem to have bettered the situation.

Commented:
Since my automated request for attention has been closed on a comment by the mod I shall respond to it here.

The asker has not responded and neither has anyone else (including you) to the moderator request for recommendations.

My comment was present both in the original question and in the request for attention.

..struggling to see what is wrong with the current disposition other than you feel you should have got some points

The question was already closed with my answer as one of the assisted solutions. The page editor reopened the question without a request from any of the participants. The question was again closed with my post no longer selected as an answer.

I, at this point demand an explanation for reopening the question.

Saqib

Do more with