Remove hyperlinks from excel

[newexcler]

Hi Experts,
I have stuff copied from site to do some analysis. The categories are in the font: Calibri Light (Heading) and others are Calibri Body and
Calibri Light header is hyperlink and I am not able to get rid off. I selected entire sheet and removed hyperlinks and now I do not even have "Remove Hyperlink option"
I do not want to change the excel settings to disable links. I just want text in this so that I can do some excel analysis.

I am bit puzzled, is there conditions and options to remove all hyperlinks from excel sheet.

Any help would be appreciated.

 

[richh]

Hi new,

Try copying the cell and pasting it back into itself using the Paste Special - Values option. That should retain the text/numbers/whatever and remove formatting.

 

[Alex Blakenburg]

I am unclear as to what you are trying to achieve here.
It sound to me that you have already successfully removed the Hyperlinks.

Are you just wanting to convert everything that is Calibri Light header to a different Font style ?

What @richh suggested leaves any existing formatting in place. It will remove the hyperlinks but if "Remove Hyperlinks" is already greyed you have already done that.

What I think may have happened is that you used "Clear Hyperlinks".
This removes the hyperlink itself but not the formatting, so the Calibri Light with the underscore gets left behind.
But what you wanted is "Remove Hyperlinks" which BOTH Removes the Hyperlink AND resets the format back to the Normal style.

 

[newexcler]

Hi new,

Try copying the cell and pasting it back into itself using the Paste Special - Values option. That should retain the text/numbers/whatever and remove formatting.

I tried that trick, unfortunately it is copying back with existing format. As excel thinks there are no hyperlinks but it's there.

 

[newexcler]

I am unclear as to what you are trying to achieve here.
It sound to me that you have already successfully removed the Hyperlinks.

Are you just wanting to convert everything that is Calibri Light header to a different Font style ?

What @richh suggested leaves any existing formatting in place. It will remove the hyperlinks but if "Remove Hyperlinks" is already greyed you have already done that.

What I think may have happened is that you used "Clear Hyperlinks".
This removes the hyperlink itself but not the formatting, so the Calibri Light with the underscore gets left behind.
But what you wanted is "Remove Hyperlinks" which BOTH Removes the Hyperlink AND resets the format back to the Normal style.

Hi Alex,
Two Problems:
1. Formatting Issues, I am not able to delete the hyperlinks, removed hyperlinks, copied back as values, text and tried different ways, only way I got rid of hyperlinks is to copy the data in text and copy back but that way I loose my format, I need to search, the bold is my category type I need to hold on for analysis
2nd problem: Now that I have the category as Calibri light heading, All I want is to search for them and colour code to use as filter. But strangely excel is not able to find it, I suspect because they have hyperlinks in them? not sure

 

[Alex Blakenburg]

1) Might need access to the spreadsheet, both clear and remove with the whole sheet selected should get rid of the hyperlink.
I assume you are saying if you hover over the cell you still get the hand and that edit hyperlink still shows a link in the address field.
2) If you use find and in the format drop down > select choose format from cell > then select a cell with the format you are after > then find all. Does that work ?
(since hyperlinks are not an option in the find box, I think it only looks for the formatting and whether or not it is a hyperlink is irrelevant)

I can see a bit of an overlap with your other post

Since you are getting the information from a web site, have you tried Power Query to see if that gives you a better result ?

 

[richh]

I tried that trick, unfortunately it is copying back with existing format. As excel thinks there are no hyperlinks but it's there.

I'm a bit confused as to what you want to accomplish. My understanding is this:

• You are copying formatted text and pasting it into an Excel worksheet
• You want to remove the hyperlinks but retain the cells' formatting
  • That should be done by pasting special - values, not just pasting (Control+V)
• You want to retain Bold formatting for the category type
  • I would imagine that the Category Type field is a single column. If so, does setting a column-level format work?
• You want to filter by cell color
  • Set a filter on the column and select the color.

Perhaps the cells are or were pre-formatted. Clear Formats should clear everything - Bold, Italics, Underline, Font Color, etc. What I think may be the issue is that you may have created a custom formatting style in the Home > Styles pane.

 

[Alex Blakenburg]

@richh
newexcler has another overlapping post which gives a bit more insight into what they is trying to do.

I have stuff copied from site to do some analysis. It has two columns, first is Type and and second is category but a few categories got in the category list.

The OP seems to be hoping to use the formatting to identify items that are in the wrong column.
I have suggested trying Power Query to get a cleaner download and avoid the issue of having to clean the data after the fact.

I think we would need the web site URL to investigate options for the download.
If there are no better options then we would need the OP's raw Excel download version to investigate data cleansing options

 

[newexcler]

I'm a bit confused as to what you want to accomplish. My understanding is this:

• You are copying formatted text and pasting it into an Excel worksheet
• You want to remove the hyperlinks but retain the cells' formatting
  • That should be done by pasting special - values, not just pasting (Control+V)
• You want to retain Bold formatting for the category type
  • I would imagine that the Category Type field is a single column. If so, does setting a column-level format work?
• You want to filter by cell color
  • Set a filter on the column and select the color.

Perhaps the cells are or were pre-formatted. Clear Formats should clear everything - Bold, Italics, Underline, Font Color, etc. What I think may be the issue is that you may have created a custom formatting style in the Home > Styles pane.

Hi Richh,
Thank you and Alex mentioned, as identifying the format is a different problem to removing hyperlinks, I have already used to power query to sort out these issues but was curious about excel stuff, what I am missing. The category type and description and issue I face is with single column. The complexity is there are hundreds of categories which are Calibre light header. But I resolved this using power query

 

[newexcler]

1) Might need access to the spreadsheet, both clear and remove with the whole sheet selected should get rid of the hyperlink.
I assume you are saying if you hover over the cell you still get the hand and that edit hyperlink still shows a link in the address field.
2) If you use find and in the format drop down > select choose format from cell > then select a cell with the format you are after > then find all. Does that work ?
(since hyperlinks are not an option in the find box, I think it only looks for the formatting and whether or not it is a hyperlink is irrelevant)

I can see a bit of an overlap with your other post

Since you are getting the information from a web site, have you tried Power Query to see if that gives you a better result ?

Hi Alex, I used the power query, used the logic of add column conditional if Category Type is NOT null and description is NULL, copy category Type and used fill down to sort and used as Category, Retaining Name, description, and Category. But was intrigued by excel unable to delete hyperlinks at the click of the button. The removed Hyperlink disappeared, giving the user the impression that there are no hyperlinks in the spread sheet. I could have posted it but has confidential information. The site is our Application. But Thanks a lot for helping me.