How to convert number to text in excel

turbot_yu
turbot_yu used Ask the Experts™
on
Need to convert a whole column, and with a '0' in front.
Comment
Watch Question

Do more with

Expert Office
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
from microsoft, for how to convert no to text
http://support.microsoft.com/kb/213360
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

=""&A1


Saqib
sorry

="0"&A1

Author

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")

If that does not work for you, please post your file.
Book4.xls

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
I thought that the problem was with non integer numbers then something like this would be required.

                            ="0" & TEXT(A1,"0.??????????????")
Is that an improvement on

="0"&A1
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.
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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial