Report

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

Ask a question Manohar - Last answered on Dec 5, 2014 at 01:58 AM by Hassan
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
Helpful
+11
plus moins
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))
Was this answer helpful?  
Manohar- Apr 13, 2009 at 04:48 AM
Hi Mubashir Aziz,
Thanks a lot for your help.I got the solutuion for my problem.
PUSHKAR NARAYAN- Aug 11, 2009 at 01:21 AM
i did not find solution as I do as suggested but problem not solved

Thanks
Pushkar
VK- Jul 5, 2011 at 07:24 AM
Thanks for sharing it. It helped a lot
KRISHNA- Aug 31, 2013 at 03:59 AM
Thanks for sharing it
Hassan- Dec 5, 2014 at 01:58 AM
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!