QUICK! Excel help needed!

1ddqd

Here's what I have - a set of data exported from a program, organized into nice little cells.

I need to calculate some totals based on these numbers, but excel comes up with 0.

If I double click (to edit) any of these values, then press enter (without doing anything) then the text seems to be converted to a readable entry for the formula. I have to "edit" each cell to get it to do this.

Here's an example:

The highlighted entries I've double clicked to "edit" and pressed Enter on - these will add up.


The highlighted entries here have yet to be "edited" - these will NOT add up (come to 00:00:00)


When I do this:


the time converts to a "readable" format for the formula.

I can't do this to the 4,000 plus cells individually. How do I convert them? I *have* tried changing the format to each column to the proper time (the time format that says "37:30:55")

DiannaoChong

can you export without the leading 0's? Its pulling the numbers in as text, and when you hit enter on the cell, it re-reads the data as a number, and lets it get calculated.

My only other suggestion is writing a macro to enter the cell, and leave and go to the next and repeat, and just let it run. this is the brute force method but will work.

edit:Another option, make a new column, and have it do a convert function on the cell, copy the formula down, and that should work for each column.

1ddqd

The macro would be awesome but I don't know how to write one.

DiannaoChong

Do you know how to record a macro?

if you find the button, once you are recording(already have the cell highlighted):
hit F2
Hit enter
Hit the down arrow to go to the cell bellow

Now stop recording.

go to edit the macro (open the menu, instead of running it, click edit instead)

in the new menu you will see lines of code, at the end (before the lines that say end [whatever]), type "call [x]" without "'s, and [x] is the exact name of the macro. save it and then run the macro. It should run through every cell in a column, and then give you an error. This is fine, it still did its job. it takes time, and I probably fucked up a step.

I am doing this without being able to see an excel spreadsheet. I am also assuming you are not using some other software, but actual ms excel. Sorry if it dosent work. I can write a macro an detailed instructions once i get to work tomarrow.

bigwah

Select the column, format, set as number.

1ddqd

DiannaoChong wrote: »

Do you know how to record a macro?

if you find the button, once you are recording(already have the cell highlighted):
hit F2
Hit enter
Hit the down arrow to go to the cell bellow

Now stop recording.

go to edit the macro (open the menu, instead of running it, click edit instead)

in the new menu you will see lines of code, at the end (before the lines that say end [whatever]), type "call [x]" without "'s, and [x] is the exact name of the macro. save it and then run the macro. It should run through every cell in a column, and then give you an error. This is fine, it still did its job. it takes time, and I probably fucked up a step.

I am doing this without being able to see an excel spreadsheet. I am also assuming you are not using some other software, but actual ms excel. Sorry if it dosent work. I can write a macro an detailed instructions once i get to work tomarrow.

This sounds fantastic. I'll give it a try

1ddqd

Ok, update. I tried the macro thing but when I saw the code something caught my eye:


When I insert the Call function, it repeats, but it constantly goes to that entered amount, instead of just selecting, pressing enter, and moving down.

DiannaoChong

Try what bigwah said, and ill work on your macro, I cant exactly remember the syntax needed, but I know around about what you need to put in there. it recorded it differently then how earlier versions of excel would I think.

You want something like... (work in progres)

sub macro1()
range(activecell.column(), activecell.row() + 1).activate
call Macro1()
end sub

I don't think that will work, theres something wonky about the functions with activecell that I am missing, if someone else knows what is wrong with that please chime in, otherwise im stuck until tomarrow.

Edit: Did you try copying the column, and pasting it in another column? or making a formula in the next column equaling the value, then pasting them as value? (if A1 is to get fixed, in B1 type =A1, and either you will get your value, or you can copy and paste the entire column as a value removing the function, and that might do it).

gnombol

Try to save your spreadsheet as a CSV file and load it again.

(Here's my theory: even though those are time values somehow Excel is convinced they are text, but when you press 'enter' after each one, Excel re-interprets the contents and recognizes they are times. By exporting and reimporting it you can make it re-interpret the whole spreadsheet at once. At least, it works on a small sample I tried.)

tsmvengy

Select all cells
Right click -> format cells
Select "Time"
Pick the format you want (00:00:00)

Or you can pick "custom format" and select h:mm:ss

DiannaoChong

If he formats the cells, it will still read them as text I believe. Thats the entire reason he sees leading zeroes when he opens it.

tsmvengy

DiannaoChong wrote: »

If he formats the cells, it will still read them as text I believe. Thats the entire reason he sees leading zeroes when he opens it.

Hmm, yeah

Try copying the cells to notepad (all at once) then copy>Paste Special into excel.

DiannaoChong

DiannaoChong wrote: »

Try what bigwah said, and ill work on your macro, I cant exactly remember the syntax needed, but I know around about what you need to put in there. it recorded it differently then how earlier versions of excel would I think.

You want something like... (work in progres)

sub macro1()
range(activecell.column(), activecell.row() + 1).activate
call Macro1()
end sub

I don't think that will work, theres something wonky about the functions with activecell that I am missing, if someone else knows what is wrong with that please chime in, otherwise im stuck until tomarrow.

Edit: Did you try copying the column, and pasting it in another column? or making a formula in the next column equaling the value, then pasting them as value? (if A1 is to get fixed, in B1 type =A1, and either you will get your value, or you can copy and paste the entire column as a value removing the function, and that might do it).

Sub macro1()
   ActiveCell.Value = ActiveCell.Value
   ActiveCell.Offset(1, 0).Activate
   Call macro1
End Sub

Just ran this on some test data and it worked for me. Let me know if that works, just paste this in instead of what you have, and run until it breaks for each column.

Cauld

I've come across this before, I found this less than elegant solution. Select the columns one at a time, go to Data -> Text to Columns, click through making sure the format is set to 'general' and then click finish. That should do it. Make sure the default dilimeted method doesn't create multiple columns.