On Wed, 11 Apr 2012 17:13:46 +0000, ddefina wrote:


Hi,

I have an excel problem that I've been working on for a few minutes, and
thought perhaps someone here knows a good formula to simplify my work.

I have a column of 10 digit numbers. In each number, periods have to be
added at intervals of 4, 2, and 4 digits. For example, if the number is
1234567890, than it needs to be broken down to, 1234.56.7890

Does anyone know an easy way to do this? I thought it might be a matter
of recording a macro to divide the numbers into columns, add the the
periods, than combine them back into an output column, but I'm sure
there has to be an easier way. Thanks!


=TEXT(A1,"0000\.00\.0000")

will return the numbers in that format.

You can then also copy/paste special: Values to have these as stand-alone without the formula. You may need to format the column into which you are pasting the values as TEXT before doing the pasting.

If you just want the numbers to display as above, but still retain their numeric qualities, then use the above format string as a custom format.

Format/Cells/Number Custom Type: 0000\.00\.0000