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?

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

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

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
tinh20012002Commented:
from microsoft, for how to convert no to text
http://support.microsoft.com/kb/213360
0
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!

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