Report

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

Ask a question Manohar - Last answered 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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!