Display cells with text or numbers, with a space every 4 digits for easy reading, and differenciate number and letters

[ob2ob2]

Hi Mr. Excel,

I have a columns of text and numbers, for about 20 digits long, as "Part numbers" of products. To avoid the operator error and to improve readability, is it possible to add a space every 4 digits, while retaining the original value of the cell, for the copy and paste purpose?

I couldn't find how to do it in the cell formatting settings, is there a way to do it without VBA?

Also, operators often make mistakes between "0" (zero) and "O" (Upper or lower case of the letter O), "1" (One) and "l" (Lower case of the letter L). Is there a way to display the numbers and letters differently (maybe different colour?), while retaining the cell's original value (for copy and paste), without using VBA?

Many thanks...

 

[AlanY]

with the 20 digits in A1, try this

=MID($A$1,1,4)&" "&MID($A$1,5,4)&" "&MID($A$1,9,4)&" "&MID($A$1,13,4)&" "&MID($A$1,17,4)

 

[bosco_yip]

Or this :

=REPLACE(REPLACE(REPLACE(REPLACE(A1,17,0," "),13,0," "),9,0," "),5,0," ")

 

[ob2ob2]

Thank you both. These will work as a separate column. When the operator looks at this column (with space inserted every 4 digits), if the user hit Ctrl-C to copy, it will copy the value with the space together. However, if there is a solution that when the user hit Ctrl-C, the original value (without space inserted) can be copied, that will be excellent.

If I right-click on the cell(s) and choose "Format Cells", in the Number tab, go to Custom..., those are all formatting related to numbers, dates...etc. Is there a way to do this formatting there? What symbol can be used to representing a text digit?

Many thanks again!