Someone sent me a sheet with cells in column as follows:
2008-1
2008-2
2008-100
2008-200
2008-1000
2008-1009
2008-20020
2008-30050
I want to convert to:
2008-00001
2008-00002
2008-00100
2008-00200
2008-01000
2008-01009
2008-20020
2008-30050
The issue I am running into is the way the cells are formatted. It won't sort correctly in the current format. Thanks
Last edited by maacmaac; 02-03-2012 at 11:50 AM.
See this thread for something similar to what you want:
http://www.excelforum.com/excel-gene...html?p=2686832
It uses a UDF called PadNum.
Regards, TMS
Try:
=LEFT(A1,FIND("-",A1))&TEXT(MID(A1,FIND("-",A1)+1,100),"00000")
copied down
You can then copy/paste special >> values to remove formulas
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi maacmaac,
You can use following formula:-
Sample file is also attached for your review. Thanks.=LEFT(A1,5)&REPT(0,5-LEN(MID(A1,SEARCH("-",A1)+1,6)))&MID(A1,SEARCH("-",A1)+1,6)
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Lots of good comments. Didn't expect so many responses but thanks to all who responded.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks