Excel: Moving Alternate Rows or Copying Text from Alternate Cells

Quick tip: This has helped me a lot! I was extracting hyperlinks and I needed to get texts associated in a separate column. This was the data I had:

Column A, cell 1: (Website name1)

Column A, cell 2: (This is the website's link) - note that the word link was a hypertext that's why I needed to use the macros mentioned above.

Column A, cell 3: (Website name2)

Column A, cell 4: (This is the website's link) - note that the word link was a hypertext that's why I needed to use the macros mentioned above.

What I want:

Column A: Website Name

Column B. extracted 'Link' or URL so to speak

What I did:

On a new column (C), I keyed in 

=INDEX(A:A,ROW()*2)

and double click / drag down;

Result:

Column C, cell 1: Website1

Column C, cell 2: Website2

Column C, cell 2: Website3

Column C, cell 2: Website4

I just did some "copy as text pasting" and remove blanks and viola!

Hope this helps. Well if you want to be a macro superstar you can always use:

Sub Test()

Dim iLastRow As Long

Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

If (iLastRow \ 2) * 2 <> iLastRow Then

iLastRow = iLastRow - 1

End If

For i = cLastRow To 2 Step -2

Cells(i - 1, "B").Value = Cells(i, "A").Value

Rows(i).Delete

Next i

End Sub

Enjoy! =)

Extract HyperLinks in Excel

It's been a while since my last post - I know. :))

Here's a quick macros and function I find very helpful and would like to share with you.

Copy and paste the following macros function for your sheet in Excel:


Function HyperLinkText(pRange As Range) As String

   Dim ST1 As String
   Dim ST2 As String
   
   If pRange.Hyperlinks.Count = 0 Then
      Exit Function
   End If
   
   ST1 = pRange.Hyperlinks(1).Address
   ST2 = pRange.Hyperlinks(1).SubAddress
   
   If ST2 <> "" Then
      ST1 = "[" & ST1 & "]" & ST2
   End If
   
   HyperLinkText = ST1
   
End Function


After that, you can easily extract by keying in a new function. Let's say column A is where your Hyper Links are:

=HyperLinkText(A1)

Copy and paste the above new function in column B and viola! :)

Hope this helps!