Another Post about entering 16 digit numbers

[mcfancypants]

Hello Friends,

I'm going to challenge everyone about their expertiese again! I have 16 digit numbers to enter into excel. I am aware of a few fixes that will allow the number to be entered without the last digit rounding to zero.

add the ' in front of the number

or

formatting the cell as text before you type the number.

HOWEVER, is there a way to have the number formatted with spaces (without typing the additional spaces or dashes)?

If I type the ' in front of the numbers it still runs the numbers all together
If I format as text, it's great, but again I have to manually type the spaces.


I would like to be able to type '1234123412341234 and have it appear as '1234 1234 1234 1234 with spaces automatically put in. Even if I have to type it in one cell and paste it into another?

Any way to automate the spaces???

 

[barry houdini]

You could format A2 down as text and then enter your numbers in there - use this formula in B2 copied down to get the required spaces

=REPLACE(REPLACE(REPLACE(A2,5,0," "),10,0," "),15,0," ")

 

[JLGWhiz]

You can custom format the column or a range within the column. Just select the range where you will be entering the 16 digit numbers. Then Right click on the sheet somewhere and click 'Format Cells' in the pop up menu. You can also access it on the ribbon under general. Select custom by clicking it, then in the small pane above the array of custom formats, you will see the same characters as those highlighted below. Just delete the characters in the small pane and type in the format you want. e.g. if you want groups of four charactes separated by a single space then type:

#### #### #### ###0

The pound symbol represents a number and the zero indicates where the number ends. You can put the spaces anyhere in the sequence. I just arbitrarily used groups of four. Click OK and that range will then automatically format your numbers for you.

 

[Scott Huish]

You can custom format the column or a range within the column. Just select the range where you will be entering the 16 digit numbers. Then Right click on the sheet somewhere and click 'Format Cells' in the pop up menu. You can also access it on the ribbon under general. Select custom by clicking it, then in the small pane above the array of custom formats, you will see the same characters as those highlighted below. Just delete the characters in the small pane and type in the format you want. e.g. if you want groups of four charactes separated by a single space then type:

The pound symbol represents a number and the zero indicates where the number ends. You can put the spaces anyhere in the sequence. I just arbitrarily used groups of four. Click OK and that range will then automatically format your numbers for you.

Excel only handles whole numbers up to 15 digits, this will not work.

 

[JLGWhiz]

Excel only handles whole numbers up to 15 digits, this will not work.

that is really odd, because it seemed to work when I tried it. I formatted the range, then typed a 16 digit number into it and it diplayed the numbr in groups of four digits. But I am having cataracts removed next month, so maybe i was just seeing things.

Nope you are correct. It puts a zero at the end of the group and truncates the actual entry. I stand corredted.

 

[mcfancypants]

Houdini - your name is perfect for you because you are a true Houdini. Your solution worked perfectly. Thanks!!!

JLGWhiz, I appreciate your efforts!