Report

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

Ask a question 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
See more 
Helpful
+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

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

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