title: # in hyperlink prevent the copy of full hyperlink
body: <p>I have made a macro that is copying over hyperlinks from one column to another. </p>
  
  <p>Macro works just fine however <strong>hyperlinks contain '#' in the middle of them</strong> so lets say:</p>
  
  <pre><code>https://website.com/nu#somethingsomething
  </code></pre>
  
  <p>which makes the macro copy just first part of it i.e.:</p>
  
  <pre><code>https://website.com/nu
  </code></pre>
  
  <p>Any ideas how to make Excel ignore '#' (if possible at all) and <strong>copy the entire value of hyperlink</strong>?</p>
  
  <p>VBA code:</p>
  
  <pre><code>Sub MilestonesHyperlink()
  
  For i = 2 To 62 'Number of rows
  
  If Range("B" &amp; i).Value &lt;&gt; "" And Range("A" &amp; i).Value &lt;&gt; "" Then 'Copy from column B to A
  
      Dim newLink As String
  
      If Range("B" &amp; i).Hyperlinks.Count = 1 Then
      newLink = Range("B" &amp; i).Hyperlinks(1).Address
      Range("A" &amp; i).Hyperlinks.Add anchor:=Range("A" &amp; i), Address:=Range("A" &amp; i)
      Range("A" &amp; i).Hyperlinks(1).Address = newLink
      End If
  End If
  
  Next i
  
  End Sub
  </code></pre>