5 Ways To Convert Hyperlink To Text In Excel

In today's digital age, working with hyperlinks in Excel is a common task. However, there are times when you need to extract the text from a hyperlink, rather than the link itself. Whether you're dealing with a large dataset or a small list, converting hyperlinks to text can be a tedious task if you don't know the right techniques. In this article, we'll explore five ways to convert hyperlinks to text in Excel, making your workflow more efficient and saving you time.

Why Convert Hyperlinks to Text?

Before we dive into the methods, let's quickly discuss why you might need to convert hyperlinks to text. Here are a few scenarios:

• You want to remove the hyperlink formatting and keep only the text.
• You need to use the text in a formula or function.
• You want to copy the text into another application or document.
• You're dealing with a large dataset and need to extract specific information from the hyperlinks.

Method 1: Using the HYPERLINK Function

The HYPERLINK function in Excel allows you to create a hyperlink, but you can also use it to extract the text from an existing hyperlink. Here's how:

1. Select the cell that contains the hyperlink.
2. Go to a new cell where you want to extract the text.
3. Type =HYPERLINK(cell) and press Enter.
4. The text will be extracted and displayed in the new cell.

Note: Replace cell with the actual cell reference that contains the hyperlink.

Method 2: Using the TEXT Function

The TEXT function in Excel converts a value to text format. You can use this function to extract the text from a hyperlink.

1. Select the cell that contains the hyperlink.
2. Go to a new cell where you want to extract the text.
3. Type =TEXT(cell) and press Enter.
4. The text will be extracted and displayed in the new cell.

Note: Replace cell with the actual cell reference that contains the hyperlink.

Method 3: Using VBA Macro

If you're comfortable with VBA macros, you can create a custom macro to extract the text from hyperlinks. Here's an example code:

Sub ExtractHyperlinkText()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = cell.Hyperlinks(1).TextToDisplay
    Next cell
End Sub

1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 or navigating to Developer > Visual Basic.
2. Insert a new module by clicking Insert > Module.
3. Paste the code into the module.
4. Save the workbook as a macro-enabled file (.xlsm).
5. Select the cells that contain the hyperlinks.
6. Run the macro by pressing Alt + F8 or navigating to Developer > Macros.

Method 4: Using Power Query

Power Query is a powerful tool in Excel that allows you to extract and transform data. You can use Power Query to extract the text from hyperlinks.

1. Select the cells that contain the hyperlinks.
2. Go to the Data tab and click From Table/Range.
3. In the Power Query Editor, click Add Column > Custom Column.
4. Type =Text.BeforeDelimiter([Hyperlink], "#") and click OK.
5. The text will be extracted and displayed in a new column.

Method 5: Using Find and Replace

If you only need to extract the text from a few hyperlinks, you can use the Find and Replace feature in Excel.

1. Select the cells that contain the hyperlinks.
2. Press Ctrl + H to open the Find and Replace dialog box.
3. In the Find what field, type http:// or https:// (depending on the type of hyperlink).
4. Leave the Replace with field blank.
5. Click Replace All.

The hyperlinks will be converted to text, and the formatting will be removed.

Gallery of Excel Hyperlink Text Conversion

Excel Hyperlink Text Conversion Gallery

We hope this article has helped you learn five ways to convert hyperlinks to text in Excel. Whether you're a beginner or an advanced user, these methods will save you time and improve your productivity. Don't forget to experiment with different techniques and find the one that works best for you.

What's your favorite method for converting hyperlinks to text in Excel? Share your thoughts and tips in the comments below!