Ask a question Report

Prefix 0 to all records in a column [Solved/Closed]

Manohar - Latest answer on Dec 5, 2014 01:58AM
Hello,
I want to prefix 0(zero) to all records in a particular column of an excel sheet.Please let me know how to do it.
Thanks for your time,
Manohar
Read more 
Answer
+13
moins plus
If the data in numeric then select the columns and go in

Format > Cell > Number > Custom > write 0#,##.00

your all number will have 0 prefix ....

if in excel, if you want it as text:
=if(len(a1)=1,"0"&value(a1),value(a1))

Manohar- Apr 13, 2009 04:48AM
Hi Mubashir Aziz,
Thanks a lot for your help.I got the solutuion for my problem.
PUSHKAR NARAYAN- Aug 11, 2009 01:21AM
i did not find solution as i do as suggested but problem not solved

Thanks
Pushkar
VK- Jul 5, 2011 07:24AM
Thanks for sharing it. It helped a lot
KRISHNA- Aug 31, 2013 03:59AM
Thanks for sharing it
Hassan- Dec 5, 2014 01:58AM
This will not add 0 to the number, it will just change the way of displaying the number. Meaning if you copy it to another excel and change the format, the number will show without the leading 0.

A better solution is to
- insert a col before (say A) and col after (say C) the col containing your number (consider B).
- in the col A, type 0 or 00 ... and make sure cell format is text.
-in cell C1 insert formula: =CONCATENATE(A1,B1)

now select the col C and copy paste data as Values to break the formula.
You can now delete col A & Col B.

Cheers
Hassan
This document entitled « Prefix 0 to all records in a column » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.