seperate block of data to different columns

  • Thread starter Thread starter ­}­}¥J
  • Start date Start date
­

­}­}¥J

Dear all,

I have got some data something like this:

[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[2745] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[135] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[1025] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[445] | [Blocked]
[2004-04-24 07:57:49] | From:[61.238.61.217] | Port:[6881] | [Blocked]

how can I seperate the data into several columns?

Column
A Date
B Time
C IP
D Port
E Block

Thanks.

ims
 
Data>Text to Columns with a delimiter of |

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I'd do some edit|replaces first.

replace [ with nothing
] with nothing
From: with nothing
Port: with nothing

Then do the data|text to columns like Bob describe (using delimited, |).

I'd keep the date/time in one column, but if you wanted you could insert an
adjacent (to the right) and do one more data|text to columns to separate the
date from the time (use space as the delimiter).

Or you could insert two helper columns and use formulas:

=int(a1) to get the date
=a1-int(a1) to get the time

Format each of the helper columns nicely.

­}­}¥J said:
Dear all,

I have got some data something like this:

[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[2745] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[135] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[1025] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[445] | [Blocked]
[2004-04-24 07:57:49] | From:[61.238.61.217] | Port:[6881] | [Blocked]

how can I seperate the data into several columns?

Column
A Date
B Time
C IP
D Port
E Block

Thanks.

ims
Click to expand...
 
this really is a good idea to replace [] with space before hand, thanks u 2
a lot.

ims




Dave Peterson said:
I'd do some edit|replaces first.

replace [ with nothing
] with nothing
From: with nothing
Port: with nothing

Then do the data|text to columns like Bob describe (using delimited, |).

I'd keep the date/time in one column, but if you wanted you could insert an
adjacent (to the right) and do one more data|text to columns to separate the
date from the time (use space as the delimiter).

Or you could insert two helper columns and use formulas:

=int(a1) to get the date
=a1-int(a1) to get the time

Format each of the helper columns nicely.

­}­}¥J said:
Dear all,

I have got some data something like this:

[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[2745] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[135] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[1025] | [Blocked]
[2004-04-24 07:57:34] | From:[221.127.161.233] | Port:[445] | [Blocked]
[2004-04-24 07:57:49] | From:[61.238.61.217] | Port:[6881] | [Blocked]

how can I seperate the data into several columns?

Column
A Date
B Time
C IP
D Port
E Block

Thanks.

ims
Click to expand...
Click to expand...
 
Back
Top