How to remove multiple rows in Excel which show '#######'?

Stompbox

I'm currently processing the mean wait times of 51,000 people and the way I'm doing it (I'm a complete Excel n00b so I'm probably making a lot of work for myself) is to click the first cell of the column (for example L2) and then dragging the box all the way down to the bottom of the column (L51000) in this case. I had hoped that I could just look at the bar running under the spreadsheet which should say 'Average: x Count: 51,000 Sum: y' but this isn't appearing. All that's appearing is the Count. When I look over some of the cells, some of them are filled with hashtags when they should be filled with 'hh:mm:ss'. Presumably Excel can't produce an average of all the times due to the presence of the hashtags.

I encountered this problem when I had to produce averages for a smaller spreadsheet (only around 400 people) so what I did was manually remove each row that contained a cell with the hashtags. Considering there were only around 16 rows out of the 400 that contained the hashtags it wasn't too laborious but sifting through 51,000 people would take yonks. Does anyone know how I can select all the rows containing the hashtags and remove them with a few button clicks?

MarkR

You could do a find and replace, find the ' character and replace it with nothing.

Deise Vu

Do a data sort in ascending or descending order. This should bring all your hashtags together and you can highlight and delete them in one go.

Deise Vu

PS If you are working for the the new Driving Licence people I can tell you that your average waiting time answer is wrong unless it says 'outrageous'.

Stompbox

Yes, Deise Vu, I used your system and it works. I'm kicking myself that I didn't think of it, thanks you guys.

mordeith

Excel should ignore the hashtags. I did a trial based on what you mention in your post. I presume the cells have been formatted to time? If so and they are all in the one column, then use this formula =AVERAGE(A1:A4)
Obviously you need to replace A1 with your first cell in the column and A4 with the last cell

Bosh

Might not be of relevance to this, but Excel also displays a line of hash symbols in a cell when the column isn't wide enough to show all the contents.

Dragging the column resizer to the right will rectify this & display correct data.

Just my probably irrelevant tuppence worth :P

mordeith

Bosh wrote: »

Might not be of relevance to this, but Excel also displays a line of hash symbols in a cell when the column isn't wide enough to show all the contents.

Dragging the column resizer to the right will rectify this & display correct data.

Just my probably irrelevant tuphpence worth :P

I was thinking that as well but figured if the data was in hh:mm:ss format then the length should be uniform. of course there could be incorrectly inputted data

ChunkyLover_53

I second Bosh resize your column.

Double Click of the dividing line on the right hand side, will auto resize to the last character of longest data in a cell

Stompbox

I had the information formatted as [h]:mm:ss, could that have been the cause?

Another query actually, I'm having awful trouble trying to rename the data labels (basically I wanna relabel the time values on the left hand side so they're rounded off - so 4:00:00 instead of 4:48:00). Excel won't seem to let me set my own gridline values.