Impossible Text Format

[Gideon du Toit]

I have a column that needs to display ID numbers (string) in a certain format eg. 0012315184085 needs to be displayed as 001231 5184 085 following the "000000 0000 000" format. However, when I try to create this format Excel ignores the custom format and either displays the string in its original format or it displays it in this format "00 000 000 000 00" or something similar. I have searched the net for days now and there seems to be no solution. Is Excel unable to assign custom text formats? Will I have to write vb code JUST to put a text-based number in a certain format?

 

[RoryA]

Custom number format codes don't work with text. If your codes are all numeric as in your example, you could convert them to real numbers, then apply a custom format.

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Try formatting it as "###### #### ###"

 

[Gideon du Toit]

Hi & Welcome to MrExcel.

Try formatting it as "###### #### ###"

Thanks for the response and the welcome. I did try to use the # in my conversation but it leaves out some of the "O" values. The number in the string is a South African citizen ID number, so every character has to be present and in a set format.

 

[Gideon du Toit]

Custom number format codes don't work with text. If your codes are all numeric as in your example, you could convert them to real numbers, then apply a custom format.

Thank you for your reply. I did try to convert it to a number, but that eliminates the leading zero's. The string in question is a South African citizen ID number, so the leading zero's refer to the date of birth. All of the characters need to be present in a particular format.

 

[Joe4]

Thank you for your reply. I did try to convert it to a number, but that eliminates the leading zero's. The string in question is a South African citizen ID number, so the leading zero's refer to the date of birth. All of the characters need to be present in a particular format.

Do not worry about the losing of the leading zeroes when converted to a number. When you appy the custom format of 000000 0000 000, it will add them back in, i.e.