How to Parse/extract specific info in a cell ?

[HikerLT]

I have a logfile from a automated test, that contains time-stamped entry's when each test starts.

The goal is to get the test name, and test timestamp into excel worksheet , so that I can then use excel to calculate the time each step is taking.

From the text logfile, I "awk'd" on the significantportion of the logfile lines, looking for the lines containing :

awk '/# <time/'

and exported the result to another text file.

I then pasted the text file into excel

Now the excel file consists of cells , all of the format similar to this :

# <time:17:15:40> ch8.1 CLOCK BITS clock verification #
# <time:17:16:17> ch8.2 CLOCK T1/E1 framer clock verification #
# <time:17:18:17> ch8.3 1588-FPGA,T1E1 framer,GE,SFP,10G phy recovery&GPS 10MHz clock verification #
# <time:17:22:07> ch8.4 BITS Diagnostic Test #
# <time:17:22:26> ch8.4 1PPS Diagnostic Test #
# <time:17:22:40> ch10 Load JIT tests config #

What I want to do from each of thoise cells is to extract into individual cells in columns to the right of each cell :
1) the time - for example 17:15:40

and then, less important, but good if possible :

2) the test name - for example ch8.1 CLOCK BITS clock verification


Any help appreciated !

[GeneralDisarray]

do you have an example sheet? did you type exactly what is i the cells????

[GeneralDisarray]

If that is the exact entry, try this (in another column, would pull out the time part) -- i pasted what you had in the j column...

=MID(J4,FIND(":",J4,1)+1,8)

--edit---

this brings back the 8 characters (timestamp) after the first encountered colon....

To get the part 2:


=MID(J4,FIND(">",J4,1)+2,LEN(J4)-FIND(">",J4,1)-2)

Again, assuming that's exactly what you have

[HikerLT]

PERFECT !!!


Thanks SO MUCH...this saves me hours of work retyping the info

[HikerLT]

OK,
I have another file....slightly different information...and the parse/extract comes up with an error

Here is the cell ( location B2) I am trying to extract the time information from:

5/24/2013 17:00:03

in the next cell C2 I have this formula, but it comes up as a "#value!" error

=MID(B2,FIND("2013",B2,1)+1,8)



I attached a small file with the info

[jubiesxl]

=MID(B2,FIND("2013",year(B2),1)+1,8)

[HikerLT]

=MID(B2,FIND("2013",year(B2),1)+1,8)

this is close, but isn't really providing the extracted "time" value hh:mm:ss

[GeneralDisarray]

Just use formatting - this is a number (dates and times are stored as numbers in excel) and you are attempting to use Sting Handling to format how it is displayed

See attachment. You can either just format the cell to display the number as hh:mm:ss (shown in column d) or you can use a formula which expects a number (shown in column E). It really depends on what you want to do with that column. If you use formatting, the full value is remembered but only the time part is shown. If you use the formula, the number is redfined (you lose the part left of the decimal point).



Formula to get the Time part only =TEXT(B2,"HH:MM:SS")