☰

Excel 수식을 사용하여 셀에 있는 하이퍼링크의 링크 위치를 추출할 수 있나요?

tag-icon • tag-icon microsoft-excel hyperlink
Excel 수식을 사용하여 셀에 있는 하이퍼링크의 링크 위치를 추출할 수 있나요?

하이퍼링크 위치와 다른 표시 텍스트가 포함된 하이퍼링크가 포함된 셀이 많은 스프레드시트가 있습니다.

즉:

셀 위치: A1

표시 텍스트 = "사이트 정보"

하이퍼링크 위치 = "http://www.mylocation.com"

하이퍼링크 위치의 텍스트 문자열에 액세스할 수 있는 Excel 수식이 있습니까?

이상적으로는 다음과 같습니다.

공식(A1) = "http://www.mylocation.com"

답변1

단일 셀 값에서 주소만 추출하면 되므로 다음과 같은 작은 함수가 편리하다는 것을 알았습니다.

"무차별 대입" 매크로 대신, 가리키는 하이퍼링크에 대한 URL을 추출하고 반환하는 사용자 정의 함수를 만들 수도 있습니다.

Function GetURL(rng As Range) As String
     On Error Resume Next
     GetURL = rng.Hyperlinks(1).Address 
End Function

이 경우 원하는 위치에 배치할 수 있습니다. 예를 들어 A1에 있는 하이퍼링크의 URL을 C25 셀에 나열하려면 C25 셀에 다음 수식을 입력합니다.

=GetURL(A1)

http://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html

답변2

매크로를 사용할 수 있습니다.

  • 새 통합 문서를 엽니다.
  • VBA로 들어가기(Alt+F11 누르기)
  • 새 모듈 삽입(삽입 > 모듈)
  • 아래 Excel 사용자 정의 함수를 복사하여 붙여넣으세요.
  • VBA에서 나가기(Alt+Q 누르기)

  • 이 사용자 정의 Excel 함수에 대해 다음 구문을 사용하십시오: =GetURL(cell,[default_value])

     Function GetURL(cell As range, Optional default_value As Variant)
 'Lists the Hyperlink Address for a Given Cell
 'If cell does not contain a hyperlink, return default_value
      If (cell.range("A1").Hyperlinks.Count <> 1) Then
          GetURL = default_value
      Else
          GetURL = cell.range("A1").Hyperlinks(1).Address
      End If
End Function

답변3

function EXTRACT_URL(input) {

  var range = SpreadsheetApp.getActiveSheet().getRange(input);
  var re = /^.+?\(\"(.+?)\",.+?$/;
  if (input.indexOf(':') != -1) {
    var formulas = range.getFormulas();
    for (var i in formulas) {
      for (var j in formulas[i]) {
        formulas[i][j] = formulas[i][j].replace(re, "$1");
      }
    }
    return formulas;
  } else {
    return range.getFormula().replace(re, "$1");
  }

}

답변4

@SAL과 동일한 문제가 있었습니다.

.Hyperlink개체가 실제로 주소를 구분 기호 를 만나면 분할 .Address하는 것으로 나타났습니다 . .SubAddress"#"은 그러한 구분 기호 중 하나입니다(다른 것이 있는지는 모르겠습니다).

위의 @gilly3의 의견과 다른 StackOverflow 질문을 참조하세요. hyperlinks(1).address는 부분 주소만 반환합니다.

또한 .SubAddress에 대한 Microsoft의 (불완전한) 문서: Hyperlink.SubAddress 속성

어디에도 언급되지 않은 한 가지 점은 "#" 구분 기호가 둘 중 하나에 포함되어 있지 않다는 것입니다. .Address따라서 .SubAddress실제로 전체 URL로 작동하는 주소를 원한다면 "#" 구분 기호를 다시 추가해야 합니다.

이를 통해 @Igor O에서 여러 하이퍼링크가 저장될 가능성과 "#" 추가 가능성을 포함하여 @Matthew Lock의 코드를 다음과 같이 수정했습니다.

 Function getURL(rng As Range) As String
     On Error Resume Next
     fullURL = ""
     For Each HL In rng.Hyperlinks
    
         If Len(HL.SubAddress) > 0 Then
             fullURL = fullURL & HL.Address & "#" & HL.SubAddress & " "
         Else
             fullURL = fullURL & HL.Address & " "
         End If

     Next

     getURL = fullURL

 End Function

그러면 모든 하이퍼링크의 전체 텍스트 문자열이 반환되어야 합니다(두 개 이상이면 구분되도록 끝에 공백을 추가했습니다).

관련 정보