CODEWITHSUNDEEP
excelhyperlink
Litwos
Litwos

Reputation: 1348

Check whether a cell contains a link in Excel

I am trying to check whether values in a column, say A, contain a link, and if true, in column B, I want to type a text, for example: link. I have many records (10 000), so doing this by hand will take a lot of time. Thx.

Upvotes: 2

Views: 32637

Answers (6)

Vaidotas Okulič-Kazarinas
Vaidotas Okulič-Kazarinas

Reputation: 1

As links (if we are speaking about links, not hyperlinks) are formulas, this should work (unless you also have formulas without links):

=IF(ISFORMULA(A1),"LINK","")

Upvotes: 0

josep maria porrà
josep maria porrà

Reputation: 1396

An aproximate solution without VBA:

=AND(ISREF(A1),ISNUMBER(SEARCH("http",A1,1)))

It is based on asking for two conditions:

  1. There is reference in the cell (it applies for both hyperlinks and internal references)
  2. The cell text (the link) contains the string "http", to cover both http and https

Upvotes: 0

Mariana
Mariana

Reputation: 126

  • Save your file as .xlsm to allow Macros
  • Alt + F11 to open Visual Basic
  • Insert -> Module
  • Paste this function, it returns the count of hyperlinks in a range:
Function IsHyperlink(r As Range) As Integer
IsHyperlink = r.Hyperlinks.Count
End Function

  • Alt+Q

  • Use your new function with the if conditions to display your text:

     =IF(IsHyperlink(A1),"LINK","NO LINK")

Upvotes: 10

dsneer
dsneer

Reputation: 1

There is a formula that could work, but it's based upon the length of the characters in the cell. For example, a hyperlink typically contains more characters due to the pathname. If the number of characters in your cells is always less than 30, for example, then you could write the formula 

 =If(Len(Cell("Filename",A1))>30,"True","False")

The formula will return "True" if the cell is a hyperlink. This formula assumes that the Excel file is stored in a subfolder where each folder name is referenced which adds to the length of the hyperlink.

Upvotes: 0

Litwos
Litwos

Reputation: 1348

I managed to find a formula, not on VBA.

I used =IF(HYPERLINK(A1>0), "WEB", "")

Thank you all for your time. :)

Upvotes: -5

AnalystCave.com
AnalystCave.com

Reputation: 4984

Here:

Sub Links()
    Dim lnk As Hyperlink, lnks As Hyperlinks
    Set lnks = Range("A:A").Hyperlinks
    For i = 1 To lnks.Count
        Set lnk = lnks(i)
        lnk.Range.Value = "Link"
    Next
End Sub

You need to read more on VBA if you want to use the procedure above. Please also always share your research first and at least a code stub. This was simple hence the exception.

Upvotes: 2

Related Questions