Adding Periods at Specified intervals

[ddefina]

Hi,

I have an excel problem that I've been working on for a few minutes, an
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 b
added at intervals of 4, 2, and 4 digits. For example, if the number i
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 matte
of recording a macro to divide the numbers into columns, add the th
periods, than combine them back into an output column, but I'm sur
there has to be an easier way. Thanks

 

[Spencer101]

Hi,

I have an excel problem that I've been working on for a few minutes, an
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 b
added at intervals of 4, 2, and 4 digits. For example, if the number i
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 matte
of recording a macro to divide the numbers into columns, add the th
periods, than combine them back into an output column, but I'm sur
there has to be an easier way. Thanks!

Hi,

Far easier than messing about with macros...

Assuming your original number is in cell A2, enter the following i
another cell on row 2 and copy down as necessary.

*=LEFT(A2,4)&"."&MID(A2,5,2)&"."&RIGHT(A2,4)

 

[Ron Rosenfeld]

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

 

[Gord Dibben]

Custom Format 0000"."00"."0000


Gord

 

[GS]

Spencer101 explained on 4/11/2012 :

Far easier than messing about with macros...

Huh! I don't see any macros posted for this thread...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

Spencer101 explained on 4/11/2012 :

Huh! I don't see any macros posted for this thread...

The OP wondered whether a macro might be appropriate. That's probably what he was referring to.

I find interesting that this post did not appear for at least four or five hours after mine and Gord's, yet its time stamp, and order, is earlier.

 

[GS]

Ron Rosenfeld was thinking very hard :

The OP wondered whether a macro might be appropriate. That's probably what
he was referring to.

I didn't understand this to be the case since he may have seen a post
that I hadn't seen yet. (Happens often when I see 'Unknown' in the
NewsReader header)

I find interesting that this post did not appear for at least four or five
hours after mine and Gord's, yet its time stamp, and order, is earlier.

Yeah, I see that often happens to many users. Not sure why, but
contributes to the above scenario every now and then...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion