Bit of help with this excel file, any ideas ? Very stuck

Combat squirrel

Combat squirrel

Combat squirrel

Soldato
Joined
7 Aug 2004
Posts
11,258
Hi I have some data in a txt file, its written like this:

16:23:20, 9.83, 0.00,31.79, 0.00,%103.5699798583984 , 0.13" *******"
16:33:21, 9.83, 0.00,31.79, 0.00,%103.6629474639893 , 0.21" *******"
16:43:23, 9.82, 0.00,31.78, 0.00,%102.2954700469971 , 0.36" *******"
16:53:24, 9.82, 0.00,31.76, 0.00,%102.3343479156494 , 0.03" *******"

(copied from the txt file)

Now when I copy and paste into excel obviously the whole lot goes into 1 column, also I cannot copy columns in one go from the txt file, select all or selecting lots of data only lets me go one row at a time and not columns (i.e. normal windows copy and paste behavior)

How can I get the data into excel splitting the columns into there own columns in excel, like:

16:53:24, ||9.82||, 0.00,31.76, ||0.00,||%102.3343479156494 ,|| 0.03"

The above for example, columns properly separated as shown by || above in excel, any ideas?
 
Paste it into Excel.
Highlight the column with it in.
Go to: Data > Text to Columns
Select Delimited > Next
Select comma > next
Set whatever formatting you require > Finish
 
Nelson said:
Paste it into Excel.
Highlight the column with it in.
Go to: Data > Text to Columns
Select Delimited > Next
Select comma > next
Set whatever formatting you require > Finish
Click to expand...

You sir are some sort of god like non corporal awesome úber being, thank you very much :D
 
Hi ! :D One more question if anyone knows....

Iv got columns of this:

0
100
200
300
400
500

And I want them to appear as:

0000
0100
0200
0300
0400
0500

The 2nd set of numbers are whats input into excel, however excel defaults them to appear as '0' then '100' etc, how do i change this ? (looked for ages cant for the life of me find out how do it)
 
0100 isn't possible as numbers but it is possible as text, replace 100 with '0100, but I don't know a way to do this for the whole column all at once :(
 
Doh, cheers anyways, now im stuck on adding a 2nd Y data series on my graph, this things impossible to use :mad:, I looked in the help file it says while on chart view go to format and 'add 2nd series' its not in the menu as it says it will be in excel ! dag nammit
 
Combat squirrel said:
Hi ! :D One more question if anyone knows....

Iv got columns of this:

0
100
200
300
400
500

And I want them to appear as:

0000
0100
0200
0300
0400
0500

The 2nd set of numbers are whats input into excel, however excel defaults them to appear as '0' then '100' etc, how do i change this ? (looked for ages cant for the life of me find out how do it)
Click to expand...

Right-click on the cells, Format cells, Number tab, Custom, 0000

That will make all cells show as 4 digits.
 
chrismox said:
Right-click on the cells, Format cells, Number tab, Custom, 0000

That will make all cells show as 4 digits.
Click to expand...
Doh, never knew that was possible, oh well, seems like I'm still not too old to learn new stuff :p
 
chrismox said:
Right-click on the cells, Format cells, Number tab, Custom, 0000

That will make all cells show as 4 digits.
Click to expand...

Cheers sir, you are awesome :D Any ideas on how to add a 2nd 'Y' axis data to my graph, unfortunately there's like only 7/8 sets of data (numbers) in the graph, lined up to the corresponding x value which I want it to appear as, like this:
13:53:08 108.78%
14:03:08 108.77% 1.91
14:13:09 108.35%
14:23:11 108.46%
14:33:12 108.20%
14:43:12 107.11%
14:53:13 106.58%
15:03:14 107.03% 2.35
15:13:14 107.71%
15:23:15 106.86%
15:33:17 106.38%
15:43:17 106.31%
15:53:18 104.67%
16:03:19 104.61% 3.3

X Y1 Y2

Iv put underneath the sample number how Id like them to appear on my graph, Y1 is already on there obviously to make the graph and appears fine, adding Y2 correctly is making me pull my hair out :(

EDIT: the above numbers dont appear as written when posted so its columns 1,2,3 being x,y1,y2, you have to imagine it i guess ? :eek: :D
 
chrismox said:
Not sure I understand, sorry.
Click to expand...

Well the 1st 2 columns of data above make x and y in a graph I have in excel, thats fine. However the last 'data set' ill term it 'y2' (as I want it to appear on the Y column, against X) I cant put into the graph and make it work, it always goes wrong, for example:

14:03:08 Is the X value, its time, 108.77% is the current Y value 1.91 is a 2nd Y value I want to overlay on the graph, but from the data sample I took there are not many 'y 2 'values in the column but they are lined properly to the 'correct' X value, i.e. 1.91 has to appear next to/corresponding to time 14:03:08 on the graph, and so on and so forth, any ideas? greatly appreciated for help so far
 
Do you mean like this?

excelvn7.jpg
 
You sir are a genious, however I want excel to roughly display this:

excelvn7.jpg


My ACTUAL graph so far being this, I want to overlay tide depths at corresponding times:

wn.jpg


The problem being now is before I wanted tide depth 1.91 next to 14:03 for example but now I need whats below to be the 'blue bar' (which iv done) on the graph

time transmittance
13:53:08 108.78%
14:03:08 108.77%
14:13:09 108.35%
14:23:11 108.46%
14:33:12 108.20%
14:43:12 107.11%
14:53:13 106.58%
15:03:14 107.03%
15:13:14 107.71%
15:23:15 106.86%
15:33:17 106.38%
15:43:17 106.31%
15:53:18 104.67%
16:03:19 104.61%

And whats below to be a 'red bar' on the graph,

Tide H Time
2.15 0000
1.75 0100
1.87 0200
2.53 0300
3.57 0400
4.72 0500
5.65 0600
6.13 0700
6.06 0800
5.49 0900
4.59 1000
3.57 1100
2.64 1200
2.03 1300
1.91 1400
2.35 1500
3.3 1600
4.51 1700
5.66 1800
6.42 1900
6.57 2000
6.12 2100
5.2 2200
4.03 2300

What I DO NEED the graph to do is line up '1.91 to 14:00' hours and plot it correctly against the time chart on the bottom of the current 2nd graph I have pictured.

Many thanks for anyones help, im doing an ocean science degree not and I.T. one ! lol, 1st time iv ever done proper office/excel work and they expect us to just know how to do it :(
 
I got one!

Can i convert numbers in Excel like this:

1
2
3
4
5
6
etc

To 1, 2, 3, 4, 5, 6 etc (if easier into Word)

Cheers
 
Last edited:
sWiZzLe said:
I got one!

Can i convert numbers in Excel like this:

1
2
3
4
5
6
etc

To 1, 2, 3, 4, 5, 6 etc (and easier into Word)

Cheers
Click to expand...

That I can help you with, select the numbers right click ---> copy--->then you need to paste them, but select 'paste special'------->on box that comes up press 'transpose'---> ok------> happy days :)
 
@Combat squirrel, it is possible if the time on the X-axis would be the same.

You can plot a second set of data on a separate Y-axis on the right but both will use the same numbers on the X-axis (in your case time)

Like this:
excel2hl1.jpg
 
What you can try is make the X-axis a lot longer with intervals of 10 minutes instead of one hour intervals, that way you can plot both the values on the timeline.
 
You must log in or register to reply here.
Back
Top Bottom