Ping: FM the excel guru

Hobbes- · 08-27-2004, 11:37 PM

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 · 08-30-2004, 08:29 AM

Highlight the column.

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

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

rickJ · 09-01-2004, 02:15 AM

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- · 09-01-2004, 06:42 AM

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 · 09-01-2004, 08:00 AM

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 · 09-01-2004, 08:35 AM

Quote:

Originally Posted by 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?

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 · 09-01-2004, 08:41 AM

Quote:

Originally Posted by FrogMan

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 · 09-01-2004, 08:45 AM

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 · 09-01-2004, 09:07 AM

Quote:

Originally Posted by 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

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

FrogMan · 09-01-2004, 09:10 AM

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 · 09-01-2004, 09:16 AM

Quote:

Originally Posted by 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

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 · 09-01-2004, 09:32 AM

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 · 09-01-2004, 02:50 PM

Quote:

Originally Posted by FrogMan

you're my hero, FM...

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

08-30-2004, 08:29 AM	#2
KevinNU7 College Starter Join Date: May 2003 Location: Beantown	Highlight the column. Go to Edit --> Replace, or Hit Ctl-H In the Find What: Row type in " Leave the replce row blank __________________ Boston Bashers - III.14 - (8347)

09-01-2004, 06:42 AM	#4
Hobbes- High School Varsity Join Date: Aug 2003 Location: Philly	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. __________________ Owner of Fockers (71987) Death to 8 Ball!

09-01-2004, 08:00 AM	#5
saintjo College Benchwarmer Join Date: Oct 2003 Location: St. Louis, MO	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? __________________ http://www.MaloMart.com

09-01-2004, 08:45 AM	#8
FrogMan Hattrick Moderator Join Date: Jan 2003 Location: Pintendre, Qc, Canada	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 __________________ A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog...

09-01-2004, 09:10 AM	#10
FrogMan Hattrick Moderator Join Date: Jan 2003 Location: Pintendre, Qc, Canada	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 __________________ A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog...

09-01-2004, 02:15 AM	#3
rickJ Mascot Join Date: May 2004 Location: Austin, TX	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.

09-01-2004, 09:32 AM	#12
FrogMan Hattrick Moderator Join Date: Jan 2003 Location: Pintendre, Qc, Canada	ahh, good to see things "magically work" like that... If anybody else has got an excel question, we can keep them in here... FM __________________ A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog...

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)