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!

Hi,

Far easier than messing about with macros...

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

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

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

Custom Format 0000"."00"."0000


Gord

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!

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

On Thu, 12 Apr 2012 03:11:15 -0400, GS wrote:

Spencer101 explained on 4/11/2012 :
Far easier than messing about with macros...

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.

I didn't say there were. The original question included the line "I thought it might be a matter of recording a macro", so I was pointing out that was not necessary!!!

Ron Rosenfeld was thinking very hard :
On Thu, 12 Apr 2012 03:11:15 -0400, GS wrote:

Spencer101 explained on 4/11/2012 :
Far easier than messing about with macros...

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 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