data format and reference question

**MattW87** · 02-26-2008, 01:13 PM

Hi all,

I am an electronic hard/software developer and am having some trouble with data analyzation in Excel.

I have a file in ASCII Format that includes some measurement results. The columns are seperated with [tab] and the first four/five lines are filled with comments, marked with a preceding '#'.

The file is imported just right, all values seem to be recognized and the resulting graphs look pretty.

1st Problem:

The first column includes the relative time when the measurement was done. Unfortunately this is in format hhh:mm:ss. For some reason this data format is not selectable in Excel and so I tried to define it with [h]:mm:ss . Unfortunately the data cells in my file look like this: "000:26:00". Excel does not recognize this when I set the data format to [h]:mm:ss, but it would recognize "0:26:00". So I guess the preceding '0's produce the trouble.
Is there some workaround about this problem? I really do not want to write a seperate program in Python (because that's what I know) to remove the preceding zeros... I want Excel to recognize this format!

2nd Problem:

I need to define manually specific data ranges. Let's say in A4:A121 it should be possible to define/calculate the '4' and '121'. So what I'd need is something like A[$B$2]:A[$B$3]. So the starting point ('4') of the sequence can be given by typing it into the B2. Unfortunately I failed to find a feature like that in Excel... Any ideas?

Best regards,
Matt

**NBVC** · 02-26-2008, 01:23 PM

Problem 1:

You will have to create a temporary column that converts the time to Excel recognized time: Use formula: =MID(A1,2,255)+0 copied down, where A1 houses first time to convert.... then you can copy >> paste special >> values over the original column and delete the temporary column

Problem 2:

Use INDIRECT: e.g. =INDIRECT("A"&B2&":A"&B3)