Mass delete mailto: entries in cells,

[Markyboy]

I have an accounts spreadsheet that I copy and paste customers names and addies into from the website back end sales information.

I do not copy e-mail addresses.

I have a mailto: with an e-mail address appear in the file in lots of places, it seems I delete it from some cells and it appears in others, my file is infested with the things now.

I can delete one by one, but this would take me weeks any ideas of how I can ctrl a select all cells and mass delete these things.

I am face with making a brand new accounts file which is a lot of work.

Thanks in advance

[Simon Lloyd]

Do you want to clear the cell contents or delete the cell?, if deleting the cell how should the data move? up/down/left/right? do you want to delete the entire row? is Mailto: the only thing in the cell? can you click the mailto like a hyperlink?

[Markyboy]

Hello Simon

Only the mailto: not the contents.

I can do it one by one, but there seems to be hundreds.

I have just worked out a temp solution, and recorded a macro that copied the delete action one one cell and run this through my whole sheet, only problem is its stuffed a load of formatting, but thats small beer compared to deleting one by one.

Could do with a better solution in future though,

Thanks

[Simon Lloyd]

Use excels Find/Replace, so click on EDIT, FIND in the find what box type your mailto: then click replace have nothing in it then click replace all!

[Markyboy]

Hello Simon

Tried that, but its a hidden format and not an actual cell entry, that was my first point of call lol

[Simon Lloyd]

Can you zip and post a mock sheet?

[Markyboy]

Encl sheet in zip, see address fields and product qty fields, move over the cells and hyperlink finger appears all over,

Thanks

[Simon Lloyd]

As a quick fix (i haven't looked at your sheet yet!) select all cells (click the square above row 1 and before column A) then go to INSERT, HYPERLINKS and choose remove links, then you can use the FIND/REPLACE, however if you have some hyperlinks that you need to keep then give me a short while as i am a little busy right now and i will come up with a solution for you!

[Markyboy]

Hello Simon

I looked into the insert hyperlink section to see if there is a remove option, I just looked again and cant find one anywhere??

I need all removed as dont use hyperlinks at all in these documents,

Thanks for your efforts, very appreciated

Mark

[mikerickson]

The Delete hyperlink button appears in the dialog only when one cell is selected and that cell has a hyperlink.
If you want VB, this removed all the hyperlinks on a sheet.

Please Login or Register  to view this content.