# Excel Question

**URL:** <https://boards.straightdope.com/t/excel-question/327296>\
**Category:** Factual Questions\
**Created:** [October 21, 2005, 5:41pm UTC](https://boards.straightdope.com/t/excel-question/327296 "2005-10-21T17:41:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Caissa](https://avatars.discourse-cdn.com/v4/letter/c/8c91f0/32.png) [@Caissa](https://boards.straightdope.com/u/Caissa)\
**Post date:** [October 21, 2005, 5:41pm UTC](https://boards.straightdope.com/t/excel-question/327296/1 "2005-10-21T17:41:20Z")

</div>

How do I get email addresses to resolve in a worksheet column?

I’ve got a bunch of email addresses that were imported from a data source. The email address names are correct, but they are not resolved (i.e. they are not hyperlinked; underlined and blue).

I need to have these addresses hyperlinked.

The only way I know how to do this is by double-clicking each cell, which does indeed hyperlink them. But I have hundreds of these guys. There has to be a way to highlight them all and resolve them, right?

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [October 21, 2005, 5:49pm UTC](https://boards.straightdope.com/t/excel-question/327296/2 "2005-10-21T17:49:06Z")

</div>

What happens if you run a macro that sets each cell to the value it currently has? Or, alternatively, sets a different cell to that value and then reverses the process? Does the result appear as a live hyperlink?

---

<div class="post-metadata">

**Author:** ![missbunny](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@missbunny](https://boards.straightdope.com/u/missbunny)\
**Post date:** [October 21, 2005, 5:59pm UTC](https://boards.straightdope.com/t/excel-question/327296/3 "2005-10-21T17:59:12Z")

</div>

Find an empty cell. Type an email address in there. It should hyperlink automatically. Then copy the format to all the other cells. You can do this by clicking the hyperlink cell, then click on the little paintbrush in the toolbar, and then paint all the the non-hyperlinked cells. It should take about 3 seconds to do the whole thing.

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [October 21, 2005, 6:21pm UTC](https://boards.straightdope.com/t/excel-question/327296/4 "2005-10-21T18:21:57Z")

</div>

If you’ve got enough rows that using the paint format would be difficult, here’s a macro that’ll do it. You’ll need to edit the range to match yours, and I’ve assumed that all of the email addresses are in the same column.

```auto

Sub fixit()
Dim EmAddr As String
For Each x In Range("A1", "A15")
    x.Activate
    EmAddr = x.Value
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" & x.Value, TextToDisplay:=x.Value
Next x
End Sub

```

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [October 21, 2005, 6:24pm UTC](https://boards.straightdope.com/t/excel-question/327296/5 "2005-10-21T18:24:53Z")

</div>

Oops, don’t really need these 2 lines:

Dim EmAddr As String  
EmAddr = x.Value

those were leftovers. Sorry!

Clean copy:

```auto

Sub fixit()
For Each x In Range("A1", "A15")
    x.Activate
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" & x.Value, TextToDisplay:=x.Value
Next x
End Sub

```

---

<div class="post-metadata">

**Author:** ![Caissa](https://avatars.discourse-cdn.com/v4/letter/c/8c91f0/32.png) [@Caissa](https://boards.straightdope.com/u/Caissa)\
**Post date:** [October 21, 2005, 6:38pm UTC](https://boards.straightdope.com/t/excel-question/327296/6 "2005-10-21T18:38:29Z")

</div>

> [@redtail23](#):
>
> If you’ve got enough rows that using the paint format would be difficult, here’s a macro that’ll do it. You’ll need to edit the range to match yours, and I’ve assumed that all of the email addresses are in the same column.
> 
> ```auto
> 
> Sub fixit()
> Dim EmAddr As String
> For Each x In Range("A1", "A15")
> x.Activate
> EmAddr = x.Value
> ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" & x.Value, TextToDisplay:=x.Value
> Next x
> End Sub
> 
> ```

Dude, thank you so much!

---

<div class="post-metadata">

**Author:** ![missbunny](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@missbunny](https://boards.straightdope.com/u/missbunny)\
**Post date:** [October 21, 2005, 6:39pm UTC](https://boards.straightdope.com/t/excel-question/327296/7 "2005-10-21T18:39:27Z")

</div>

IMO, I think a macro is way overkill for this. If the Format Paintbrush is too difficult, just highlight the one hyperlinked cell, Copy, select the row or column with the plain email addresses, Paste Special/Format. Really it couldn’t take more than a few seconds to do thousands of rows.

---

<div class="post-metadata">

**Author:** ![Caissa](https://avatars.discourse-cdn.com/v4/letter/c/8c91f0/32.png) [@Caissa](https://boards.straightdope.com/u/Caissa)\
**Post date:** [October 21, 2005, 6:48pm UTC](https://boards.straightdope.com/t/excel-question/327296/8 "2005-10-21T18:48:44Z")

</div>

> [@missbunny](#):
>
> IMO, I think a macro is way overkill for this. If the Format Paintbrush is too difficult, just highlight the one hyperlinked cell, Copy, select the row or column with the plain email addresses, Paste Special/Format. Really it couldn’t take more than a few seconds to do thousands of rows.

Ah, your way works nicely, too. Thanks!