I have a spreadsheet and in column A a series of numbers all the same length
example:

412381

412382

412383

412384

412385

412386

412387

I need to iterate through and insert : between the second and third digit
and fourth and fifth. The outcome should resemble the following:

41:23:81

41;23;82

41;23;83

41;23;84

41;23;85

41;23;86

41;23;87

How does one accomplish this? Thanx.

With data in A1 put this in B1 and copy down:

=LEFT(A1,2) &":" & MID(A1,3,2) & ":" & RIGHT(A1,2)


"Jlaz" wrote:

I have a spreadsheet and in column A a series of numbers all the same length
example:

412381

412382

412383

412384

412385

412386

412387

I need to iterate through and insert : between the second and third digit
and fourth and fifth. The outcome should resemble the following:

41:23:81

41;23;82

41;23;83

41;23;84

41;23;85

41;23;86

41;23;87

How does one accomplish this? Thanx.

On Fri, 10 Feb 2006 22:34:38 -0800, "Jlaz" wrote:

I have a spreadsheet and in column A a series of numbers all the same length
example:

412381

412382

412383

412384

412385

412386

412387

I need to iterate through and insert : between the second and third digit
and fourth and fifth. The outcome should resemble the following:

41:23:81

41;23;82

41;23;83

41;23;84

41;23;85

41;23;86

41;23;87

How does one accomplish this? Thanx.


If your data is in A1:An, in some column enter the formula:

=TEXT(A1,"00\:00\:00")

Then copy/drag down to row n.

Finally, select the range with the changed values, e.g. B1:Bn

Edit/copy
Select A1
Paste Special -- Values

This will replace the original so be sure you have backed up your data first.


--ron