Excel Questions

[Orior]

Is there a way to format numbers in excel so that

1
1.1
2
0.1

will be formatted exactly as listed above? Using the standard one decimal place gives

1.0
1.1
2.0
0.1

But I dont want to see things like "1.0"

Thanks in advance

[Orior]

Forget it. Just use General. How emmbarrassing!

[Billys Boots]

How emmbarrassing!

Twice in two quotes - keep up the good work. 

[Orior]

I have a blind spot to the right of the Post button. I think it says "Spell Check", but I'm not sure.

[donelli]

Have been exporting fixtures from sevrasport onto excel.
im looking to break up a cell into 3 for a long list of fixtures, eg the cell would be "Dublin v Down". Im trying to make that 3 cells with in this example "dublin" in 1, "v" the next, while "down" the third.

Any help would be appreciated

[thebigfella]

Have been exporting fixtures from sevrasport onto excel.
im looking to break up a cell into 3 for a long list of fixtures, eg the cell would be "Dublin v Down". Im trying to make that 3 cells with in this example "dublin" in 1, "v" the next, while "down" the third.

Any help would be appreciated

You could try Data>Text to Columns>Delimited by space.

[donelli]

Have been exporting fixtures from sevrasport onto excel.
im looking to break up a cell into 3 for a long list of fixtures, eg the cell would be "Dublin v Down". Im trying to make that 3 cells with in this example "dublin" in 1, "v" the next, while "down" the third.

Any help would be appreciated

You could try Data>Text to Columns>Delimited by space.

good man.
got that sorted and thanks for the prompt reply

[RMDrive]

This problem has only arisen since we switched to Office 2007. In excel I have a large table of data with column filters switched on. I want to filter the list based on "xxx" being in a particular column and then delete all rows that contain"xxx". The filtering works fine but When I select all of the filtered rows and delete, it deletes everything in between the filtered rows as well. It's like a "visible cells only" issue but it never occured in 2003.

Help please.

[glens73]

This problem has only arisen since we switched to Office 2007. In excel I have a large table of data with column filters switched on. I want to filter the list based on "xxx" being in a particular column and then delete all rows that contain"xxx". The filtering works fine but When I select all of the filtered rows and delete, it deletes everything in between the filtered rows as well. It's like a "visible cells only" issue but it never occured in 2003.

Help please.

Go to the data tab at the top and click re-apply on the sort & filter section (in the middle) then try and delete. I'm not sure if this works with deleting but with copying cells that are filtered sometimes it will select all cells and the way around it is to click the re-apply icon.

Alternatively sort the data by that column and delete those rows.

[Orior]

I have a spreadsheet with a column containing website names with hyperlinks. I want to sort and remove duplicates.

So I use the Data and Advanced Filter to copy Unique Records Only only to another column.

The copy and deduplication works okay, but I loose the hyperlink.

Is there a way of doing this and retain the hyperlink but without resorting to a macro?

[Bensars]

What about inserting a column beside the column described.

Insert a formula =if b1=a1,"dupilcate". Copy this formula all the way down.

Sort column a in alphabetical order and it should group identicals on top of each other and display duplicate in the new column. then After a manual check  sort column B (the new column you inserted) in alphabetical as well and all the duplicates should  now be grouped together. Which you can now delete.


I would also insert a column in front of the one you listed and simply number it 1 to whatever all the way down. After all the sorting and deleting you can simply sort this column again in numerical from smallest to largest to return your data to the original look ( minus the duplicates).

Then delete the two inserted columns.

[Hardy]

Just sorting, instead of filtering, preserves the hyperlinks. If you do that, all your duplicate records are grouped.

After sorting, say your data is in column A (from A2 down). In column B put a formula (from B2 down) like IF(A2=A1,0,B1+1).

Now all your duplicate records (except the first one of each set) have a zero opposite them. The wanted records have numerical values increasing from 1.

Now copy Column B and save it onto itself as Values (Copy, Paste Special, Values).

Sort by column B, ascending and all your duplicates are grouped at the top of column A and can be deleted as a block.

(Just saw Bensars's reply, but I'm posting this anyway, cos I can't stand the thought that my typing time was wasted).

[Fear ón Srath Bán]

(Just saw Bensars's reply, but I'm posting this anyway, cos I can't stand the thought that my typing time was wasted).

... lettuce/cosine I can't stand...

[Orior]

Thanks Bensars. Emmm, yes I would have come up with that eventually too. It worked a treat.

Thanks also to Hardy - its the thought that counts.

[Hardy]

(Just saw Bensars's reply, but I'm posting this anyway, cos I can't stand the thought that my typing time was wasted).

... lettuce/cosine I can't stand...

Forgive me.