Incomplete Excel Cells to be filled with specific characters while writing ID number

%3CLINGO-SUB%20id%3D%22lingo-sub-1932130%22%20slang%3D%22en-US%22%3EIncomplete%20Excel%20Cells%20to%20be%20filled%20with%20specific%20characters%20while%20writing%20ID%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932130%22%20slang%3D%22en-US%22%3EHi!%3CBR%20%2F%3EHopefully%20everyone%20of%20this%20community%20is%20well%20and%20good.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20facing%20an%20issue%20that%20I%20need%20to%20write%20an%20ID%20Card%20number%20from%20data%20given%20in%20cells%20in%20rows.%20The%20situation%20is%20this%20that%2C%20if%20the%20input%20cells%20for%20ID%20number%20is%205%20charactered%2C%20this%20is%20ok%20but%20if%20it%20is%20less%20than%205%20characters%2C%20it%20should%20write%209s%20for%20the%20rest%20of%20the%20remaining%20characters%20to%20make%20the%20characters%20number%20to%205%20for%20that%20cell%20in%20ID%20Number%2C%20is%20this%20something%20possible%20to%20do.%3CBR%20%2F%3EThanks!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1932130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1932976%22%20slang%3D%22en-US%22%3ERe%3A%20Incomplete%20Excel%20Cells%20to%20be%20filled%20with%20specific%20characters%20while%20writing%20ID%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F881793%22%20target%3D%22_blank%22%3E%40ZainAziz%3C%2FA%3E%26nbsp%3BPerhaps%20like%20this%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-11-26%20at%2007.30.52.png%22%20style%3D%22width%3A%20609px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236205i5BD7D4DC9E22F40F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-11-26%20at%2007.30.52.png%22%20alt%3D%22Screenshot%202020-11-26%20at%2007.30.52.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20B2%20and%20copied%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(CHOOSE(5-LEN(A2)%2CA2%26amp%3B9%2CA2%26amp%3B99%2CA2%26amp%3B999%2CA2%26amp%3B9999)%2CA2%26amp%3B%22%22)%2B0%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIt%20will%20deduct%20the%20number%20of%20characters%20for%20the%20input%20from%205%20and%20%3CSTRONG%3Echooses%3C%2FSTRONG%3E%20to%20attach%201%2C%202%2C%203%20or%204%209s%20to%20the%20input%20value.%20In%20the%20above%20example%2C%20I%20added%20%22%2B0%22%20in%20the%20end%20to%20force%20Excel%20to%20make%20numbers%20of%20the%20end%20result.%20If%20these%20can%20remain%20texts%2C%20jus%20leave%20out%20that%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
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!
1 Reply

@ZainAziz Perhaps like this?

Screenshot 2020-11-26 at 07.30.52.png

In B2 and copied down:

 

=IFERROR(CHOOSE(5-LEN(A2),A2&9,A2&99,A2&999,A2&9999),A2&"")+0

 

It 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.