How to convert number to text in excel

Need to convert a whole column, and with a '0' in front.
turbot_yuAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

try

=text(A1,"00000")

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

cheers, teylyn
0
 
tinh20012002Commented:
from microsoft, for how to convert no to text
http://support.microsoft.com/kb/213360
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
if you want the number as-is without imposing a format then simply use

=""&A1


Saqib
0
 
Saqib Husain, SyedEngineerCommented:
sorry

="0"&A1
0
 
turbot_yuAuthor Commented:
I tried ="0"&text(A1,"0"), but not work, the 2nd "0" seems not correct.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
can you post a few samples of your numbers? An Excel File would be best.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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
0
 
Amitkumar PSr. ConsultantCommented:
turbot_yu,

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

Q-26929132.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
amit, interesting approach. This can be done without a helper column, though:

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

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

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

="0"&A1
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
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

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

All Courses

From novice to tech pro — start learning today.