I need leading zeroes to numbers less than 5 numbers in length. HOWEVER some may have a letter at the end. SO, I am looking for a formula that will do:
1 --------->00001
12 -------->00012
123 ------->00123
1234 ----->01234
12345 ---->12345
1a -------->00001a
12b ------->00012b
123c ------>00123c
1234d ----->01234d
12345e ---->12345e
Thanks!
Try this formula
in B1 and copy down
=IF(ISNUMBER(--A1),TEXT(A1,"00000"),TEXT(LEFT(A1,LEN(A1)-1),"00000")&RIGHT(A1))
Row\Col 1 00001 12 00012 123 00123 1234 01234 12345 12345 1a 00001a 12b 00012b 123c 00123c 1234d 01234d 12345e 12345e
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Using your posted data in A1:A10
Try this formula, copied down
Actually, I can abbreviate the formula a bit more:
Please Login or Register to view this content.
Is that something you can work with?Please Login or Register to view this content.
Last edited by Ron Coderre; 07-06-2015 at 07:39 PM.
Perfect!! Thank you! This worked wonderfully.Originally Posted by AlKey
You're welcome and thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks