Thread: Parsing data

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" <brenjoe3@msn.com> 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

Thanx guys much simpler than what I had expected. I figured a loop and move
to next row type solution.

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