Forum Discussion
Incomplete Excel Cells to be filled with specific characters while writing ID number
Hi!
Hopefully everyone of this community is well and good.
I'm facing an issue that I need to write an ID Card number from data given in cells in rows. The situation is this that, if the input cells for ID number is 5 charactered, this is ok but if it is less than 5 characters, it should write 9s for the rest of the remaining characters to make the characters number to 5 for that cell in ID Number, is this something possible to do.
Thanks!
Hopefully everyone of this community is well and good.
I'm facing an issue that I need to write an ID Card number from data given in cells in rows. The situation is this that, if the input cells for ID number is 5 charactered, this is ok but if it is less than 5 characters, it should write 9s for the rest of the remaining characters to make the characters number to 5 for that cell in ID Number, is this something possible to do.
Thanks!
1 Reply
ZainAziz Perhaps like this?
In B2 and copied down:
=IFERROR(CHOOSE(5-LEN(A2),A2&9,A2&99,A2&999,A2&9999),A2&"")+0It will deduct the number of characters for the input from 5 and chooses to attach 1, 2, 3 or 4 9s to the input value. In the above example, I added "+0" in the end to force Excel to make numbers of the end result. If these can remain texts, just leave out that part.
