Ping: FM the excel guru

[Hobbes-]

hey froggy,

I have a question for ya.

My father has an excel table with 15,000 records (much like the one i had) where there are columns with just " , , " in them. That's all the whole column will have all the way to the bottom. Without manually going through and deleting all the columns is there a way to get rid of any column that has ",," as it's text. He said that they only have double comma. And all are the same. Thanks heaps bro.

-Nate

p.s. - i tried to pm froggyhead but his box seems to be full..lol

[KevinNU7]

Highlight the column.

Go to Edit --> Replace, or Hit Ctl-H

In the Find What: Row type in "
Leave the replce row blank

[rickJ]

Wouldn't that just make the whole row blank, pretty sure you can right-click the header of the row and there's an option 'delete row' that'll remove it completely. Think that's what Hobbes wanted.

[Hobbes-]

yah we got it taken care of.. thanks for the input.

u can either do a sort or a filter. my dad wanted the filter so we took care of it. :)

[saintjo]

well i have another excel question. i have a spreadsheet with a formula in a cell. if i edit the formula and change one of the cells that are references (say i want to use E2 instead of E3) the formula will now show in the cell instead of the result.
any ideas?

[FrogMan]

well i have another excel question. i have a spreadsheet with a formula in a cell. if i edit the formula and change one of the cells that are references (say i want to use E2 instead of E3) the formula will now show in the cell instead of the result.
any ideas?
The format of your cell, the one with the equation, is probably set to "text", so it shows the text of the equation once you edit it, instead of the result of the equation...

FM

[saintjo]

The format of your cell, the one with the equation, is probably set to "text", so it shows the text of the equation once you edit it, instead of the result of the equation...

FM

so what should the cell format be?

[FrogMan]

right click on the said cell, look at the "number" tab, select any other format than text, probably not "date" or "hour", but any other format should do.

Setting it as text means that even a number entered in the cell will be treated as text. For example, we have part numbers here that start with a zero, "062152" is an example. If I enter this number in a cell, it shows up as "62152", but if I set the number format of the cell as text, it shows up with the appropriate zero in front...

FM

[saintjo]

right click on the said cell, look at the "number" tab, select any other format than text, probably not "date" or "hour", but any other format should do.

Setting it as text means that even a number entered in the cell will be treated as text. For example, we have part numbers here that start with a zero, "062152" is an example. If I enter this number in a cell, it shows up as "62152", but if I set the number format of the cell as text, it shows up with the appropriate zero in front...

FM

but the answer to the formula isnt a number, it is a string.

[FrogMan]

then set the format to "standard", this will let the equation be calculated. The "text" format bypasses the equation in the cell and presents the text of the equation as text instead of calculating it as the equation that it is...

FM

[saintjo]

then set the format to "standard", this will let the equation be calculated. The "text" format bypasses the equation in the cell and presents the text of the equation as text instead of calculating it as the equation that it is...

FM
ah...turns out it wasnt the format of the cell that was the problem, but the format of the cell it was pulling from. once i set it to text it all magically worked.
thanks

[FrogMan]

ahh, good to see things "magically work" like that... :)

If anybody else has got an excel question, we can keep them in here...

FM

[finkenst]

<excel geek stuff>
you're my hero, FM...

and i was just happy to learn how to make a conditional format in some cells... :)