Jak przekonwertować kolumnę tekstowych adresów URL na aktywne hiperłącza w programie Excel?


275

Mam kolumnę w programie Excel, w której mam wszystkie wartości adresu URL witryny. Moje pytanie brzmi: chcę zmienić wartości adresu URL na aktywne linki. W tej kolumnie znajduje się około 200 wpisów z różnymi adresami URL we wszystkich komórkach. Czy istnieje sposób, w jaki mogę utworzyć aktywne hiperłącza do wszystkich komórek bez pisania makra.

Odpowiedzi:


314

Jeśli nie chcesz tworzyć makra i dopóki nie przeszkadza ci dodatkowa kolumna, po prostu utwórz nową kolumnę obok kolumny adresów URL.

W nowej kolumnie wpisz formułę =HYPERLINK(A1)(zastępując A1 dowolną komórką, którą jesteś zainteresowany). Następnie skopiuj formułę do pozostałych 200 pozycji.

UWAGA: To rozwiązanie nie działa, jeśli komórka A1 zawiera ciąg dłuższy niż 255 znaków. Powoduje #VALUE!błąd


2
Jeśli moja kolumna A zawiera 200 wartości odsyłaczy, czy można napisać formułę, która odsyłałaby 200 wartości i przepisała ją w kolumnie A, zamiast tworzyć nową kolumnę?
programista

1
Doskonały! Najlepsze jest to, że ten krok jest tak prosty, że mogę przekazać pliki CSV do laika, który może z niego łatwo wygenerować adresy URL.
Cardin Lee JH,

3
Aby było tak, że jest to jedna kolumna, po prostu skopiuj kolumnę hiperłącza i „wklej wartość” do dowolnej kolumny, którą chcesz zachować.
Mike

1
Należy zaakceptować odpowiedź, ponieważ rozwiązuje ona większość przypadków użycia.
Jay

Ukryj nieprzetworzoną kolumnę, która nie jest hiperłączem
RickAndMSFT

94

Utwórz makro jak tutaj:

W menu Narzędzia w Microsoft Excel wskaż Makro, a następnie kliknij Edytor Visual Basic. W menu Wstaw kliknij polecenie Moduł. Skopiuj i wklej ten kod do okna kodu modułu. Automatycznie nazywa się HyperAdd.

Sub HyperAdd()

    'Converts each text hyperlink selected into a working hyperlink

    For Each xCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
    Next xCell

End Sub

Po zakończeniu wklejania makra kliknij polecenie Zamknij i wróć do programu Microsoft Excel w menu Plik.

Następnie wybierz wymagane komórki, kliknij makro i kliknij uruchom.

UWAGA NIE zaznaczaj całej kolumny! Wybierz TYLKO komórki, które chcesz zmienić, na klikalne łącza, w przeciwnym razie skończysz w niekończącej się pętli i będziesz musiał ponownie uruchomić program Excel! Gotowe!


2
Dodaj Dim xCell As Range, jeśli używasz jawnej opcji. +1 za rozwiązanie, które zmienia komórkę (zgodnie z zapytaniem) i podaje kod, który to robi.
Praesagus,

To zadziałało dla mnie dobrze, pod warunkiem, że miałem około 50 000 linków w arkuszu kalkulacyjnym, które wymagały aktualizacji i musiałem to zrobić w kawałkach lub program Excel się zawiesił.
ATek,

2
Aby przekształcić adresy e-mail w hiperłącza, zmień Address:=xCell.FormulanaAddress:="mailto:" & xCell.Formula
Danny Beckett

Ponadto w przypadku adresów URL, które nie zaczynają się od http://tego, należy dodać (użyj powyższej zmiany, ale zastąp mailto:http://. W przeciwnym razie hiperłącza nie będą działać.
Danny Beckett,

1
Działa świetnie. FYI Excel 2016 ma limit 66 530 hiperłączy na arkusz, więc spodziewaj się ewentualnego błędu w czasie wykonywania, jeśli go przekroczysz. support.office.com/en-us/article/…
Christopher O'Brien

24

Oto sposób, w jaki znalazłem. Jestem na komputerze Mac przy użyciu programu Excel 2011. Jeśli w kolumnie B znajdują się wartości tekstowe, które mają być hiperłączami, umieść tę formułę w komórce C1 (lub D1 lub cokolwiek innego, o ile jest to wolna kolumna): =HYPERLINK(B1,B1) spowoduje to wstawienie hiperłącza za pomocą lokalizacja jako tekst linku i „przyjazna nazwa” jako tekst linku. Jeśli masz inną kolumnę, która ma przyjazną nazwę dla każdego linku, możesz jej również użyć. Następnie możesz ukryć kolumnę tekstową, jeśli nie chcesz jej widzieć.

Jeśli masz listę identyfikatorów czegoś, a wszystkie adresy URL były http://website.com/folder/IDtakie, jak:

A1  | B1
101 | http://website.com/folder/101
102 | http://website.com/folder/102
103 | http://website.com/folder/103
104 | http://website.com/folder/104

możesz użyć czegoś takiego =HYPERLINK("http://website.com/folder/"&A1,A1)i nie potrzebujesz listy adresów URL. Taka była moja sytuacja i działała ładnie.

Zgodnie z tym postem: http://excelhints.com/2007/06/12/hyperlink-formula-in-excel/ ta metoda będzie działać również w programie Excel 2007.


Działa w systemie Windows, Excel 2013 i jest świetną alternatywą dla dodatkowej kolumny.
mdisibio,

Do Twojej wiadomości, w przypadku Open Office powyższy błąd wystąpił, ale wydaje się, że = HYPERLINK (C1) działa, jeśli podano tylko jeden parametr
tonypdmtr

19

OK, oto rozwiązanie typu hokey, ale po prostu nie mogę wymyślić, jak przekonać Excela do oceny kolumny adresów URL jako zbiorczych hiperłączy.

  1. Utwórz formułę, ="=hyperlink(""" & A1 & """)"
  2. Zwlec
  3. Skopiuj nową kolumnę z formułą
  4. Wklej tylko wartości specjalne w oryginalnej kolumnie
  5. Kolumna kulminacyjnym, click Ctrl- H(wymienić), znajdowanie i zastępowanie =z =(jakoś siły ponownej oceny komórek).
  6. Komórki powinny być teraz klikalne jako hiperłącza. Jeśli chcesz styl niebieski / podkreślony, po prostu zaznacz wszystkie komórki i wybierz styl Hiperłącze.

Sam styl hiperłącza nie zostanie przekonwertowany na klikalne łącza, a okno dialogowe „Wstaw hiperłącze” nie może używać tekstu jako adresu dla grupy komórek. Poza tym, F2i Enterprzez wszystkich komórkach zrobi to, ale to uciążliwe dla wielu komórek.


3
Po prostu zrób = (HYPERLINK (POŚREDNIE (ADRES (ROW (), KOLUMNA () + 1)), „Sukienka” i RZĘD ())) łatwiej
Nick Turner

Uzgodnione, to najlepsza odpowiedź, która nie wymaga utrzymywania 2 kolumn.
mat

+1 za zamień „=” -> „=”. tego szukam. dzięki.
Deweloper

18

Dość prosty sposób na raczej krótkie listy:

  1. Kliknij dwukrotnie pole, w którym znajduje się adres URL
  2. Wchodzić

Masz swój link;)


14
Używam F2, aby edytować komórkę, a następnie kilkakrotnie wchodzę, aby szybko przewinąć listę. Prosty. Dziękuję Ci!
Bryan Ash

1
To plus komentarz na temat F2 doprowadził mnie do kolumny 160 hiperłączy w około minutę. Więc tak, to jest ta droga, jeśli kolumna jest wystarczająco krótka.
Trevor Brown

Nie sądzę, że może to być rozwiązanie dla kogoś, kto ma setki komórek do konwersji.
Ma3x

Mam 287 000 wierszy. Płakać.
thejohnbackes

12

Jeśli dodanie dodatkowej kolumny z hiperłączami nie jest opcją, alternatywą jest użycie zewnętrznego edytora, aby dołączyć hiperłącze do =hyperlink("i "), w celu uzyskania=hyperlink("originalCellContent")

Jeśli masz Notepad ++, jest to przepis, którego możesz użyć do wykonania tej operacji półautomatycznie:

  • Skopiuj kolumnę adresów do Notepad ++
  • Przytrzymanie ALT- SHIFTnaciśnięty, rozszerzył kursor od lewego górnego rogu do lewego dolnego rogu i pisze =hyperlink(". To dodaje =hyperlink("na początku każdego wpisu.
  • Otwórz menu „Zamień” ( Ctrl- H), aktywuj wyrażenia regularne ( ALT- G) i zamień $(koniec linii) na "\). Dodaje to zamknięty cytat i zamknięty nawias (z którym należy uciec, \gdy aktywowane są wyrażenia regularne) na końcu każdej linii.
  • Wklej dane z powrotem do Excela. W praktyce wystarczy skopiować dane i wybrać pierwszą komórkę kolumny, w której mają się znaleźć dane.

Świetna odpowiedź. Jest to jedyna droga, jeśli nie chcesz żadnych dodatkowych kolumn. Rozwiązanie może być użyte w dowolnym edytorze lub języku programowania (nie tylko Notepad ++). W programie Excel 2010 tworzy to klikalne łącza, ale musiałem ręcznie zastosować „styl formatowania hiperłączy”, aby naprawdę wyglądały jak łącza (tzn. Podkreślone na niebiesko).
absurdalny

Uwaga: Nie wymaga to notatnika ++ ... tylko ze względu na przejrzystość, możesz to zrobić w dowolnym edytorze tekstu. Sztuką jest po prostu owinięcie każdej linii =hyperlink("__linecontentgoeshere__")... dla tych, którzy mogą zostać wprowadzeni w błąd przez wzmiankę o notatniku ++
dreftymac 26.04.19

@dreftymac To daje przepis na to, jak zrobić to prawie automatycznie, a nie edytować wiersz po wierszu. Wykorzystuje on funkcję Notepad ++ do przedłużenia kursora na wielu liniach i obsługę wyrażeń regularnych; inne edytory mogą mieć podobne funkcje. Na początku mojej odpowiedzi wyjaśniam, jaki jest zamierzony rezultat, który powinien pozbyć się nieporozumień.
Antonio

1
// Wyjaśniam na początku mojej odpowiedzi, jaki jest zamierzony rezultat // Dziękuję za podkreślenie swojego punktu. Nie krytykowałem twojej odpowiedzi, po prostu uściśliłem ją dla tych, którzy mogą nic nie wiedzieć o Notepad ++, i mogą nie zdawać sobie sprawy, że jest to po prostu jeden z wielu edytorów tekstu, który potrafi to zrobić. Wiem, że zdajesz sobie z tego sprawę, ale nie wszyscy to wiedzą .
dreftymac

1
Możesz także skomponować formułę w programie Excel ="=HYPERLINK("""&A1&""")"(zakładając, że adres URL znajduje się w A1). Następnie skopiuj wynik do Notatnika i ponownie do Excela (może być lepszy sposób). Teraz masz działające hiperłącza - możesz zastosować do nich styl hiperłącza z panelu Style, aby uzyskać niebieski podkreślony wygląd. Jeśli masz etykietę ogniwem w kolumnie B, można użyć następującego wzoru: ="=HYPERLINK("""&A1&""";"""&B1&""")".
Jonáš Jančařík

7

Ta metoda działa dla mnie przy użyciu funkcji hiperłącza:

=HYPERLINK("http://"&B10,B10)

Gdzie B10jest komórka zawierająca tekstową wersję adresu URL (w tym przykładzie).


= HYPERLINK („http: //” i B10, B10) nie działał w moim programie Excel 2013, jednak = HYPERLINK („http: //” i B10) działał DID.
agibsen

5

W programie Excel 2007 w systemie Windows te kroki były najprostsze;

  1. Wybierz komórki z nieaktywnymi adresami URL
  2. Kopiuj
  3. Wklej jako hiperłącze

1
W późniejszej wersji (korzystam z pakietu Office 2011) to nie działa - po prostu dostaję hiperłącze do pliku na pulpicie (ten), a nie do adresu URL wyświetlanego w komórce. np. plik: // localhost / Users / xxxx / Desktop / Blahblahblah File Name.xlsx - Sheet1! A2
Dannid 30.01.2014

4

Zszokowałem, że Excel nie zrobił tego automatycznie, więc oto moje rozwiązanie, mam nadzieję, że będzie przydatne dla innych,

  1. Skopiuj całą kolumnę do schowka
  2. Otwórz to w Chrome lub Firefox

data:text/html,<button onclick="document.write(document.body.querySelector('textarea').value.split('\n').map(x => '<a href=\'' + x + '\'>' + x + '</a>').join('<br>'))">Linkify</button><br><textarea></textarea>

  1. Wklej kolumnę na stronie, którą właśnie otworzyłeś w przeglądarce i naciśnij „Linkify”
  2. Skopiuj wynik z karty do kolumny w programie Excel

Zamiast tego krok drugi, możesz skorzystać z poniższej strony, najpierw kliknij „Uruchom fragment kodu”, a następnie wklej na nim kolumnę

<button onclick="document.write(document.body.querySelector('textarea').value.split('\n').map(x => '<a href=\'' + x + '\'>' + x + '</a>').join('<br>'))">Linkify</button><br><textarea></textarea>


3

Dla mnie właśnie skopiowałem całą kolumnę, która ma adresy URL w formacie tekstowym, do innej aplikacji (powiedzmy Evernote), a kiedy zostały wklejone, stały się linkami, a następnie skopiowałem je z powrotem do Excela.

Jedyne, co musisz zrobić, to upewnić się, że dane, które kopiujesz, są zgodne z resztą kolumn.



2

Odkryłem, że żadna z metod tutaj nie działała, jeśli hiperłącze nie zawierało http: //, ponieważ zawierały linki do lokalnych lokalizacji.

Chciałem też oszukać skrypt, ponieważ użytkownicy nie byliby w stanie samodzielnie go utrzymać i nie byłbym dostępny.

Będzie działał tylko na komórkach w wybranym zakresie, jeśli zawierają kropkę i nie zawierają spacji. Będzie działał tylko dla maksymalnie 10 000 komórek.

Sub HyperAdd()
Dim CellsWithSpaces As String
    'Converts each text hyperlink selected into a working hyperlink
    Application.ScreenUpdating = False
    Dim NotPresent As Integer
    NotPresent = 0

    For Each xCell In Selection
        xCell.Formula = Trim(xCell.Formula)
        If xCell.Formula = "" Or InStr(xCell.Formula, ".") = NotPresent Then
        'Do nothing if the cell is blank or contains no dots
        Else
            If InStr(xCell.Formula, " ") <> 0 Then
                CellsWithSpaces = CellsWithSpaces & ", " & Replace(xCell.Address, "$", "")
                 GoTo Nextxcell
            End If

            If InStr(xCell.Formula, "http") <> 0 Then
                Hyperstring = Trim(xCell.Formula)
            Else
                Hyperstring = "http://" & Trim(xCell.Formula)
            End If

            ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=Hyperstring

        End If
        i = i + 1
        If i = 10000 Then Exit Sub
Nextxcell:
      Next xCell
    If Not CellsWithSpaces = "" Then
        MsgBox ("Please remove spaces from the following cells:" & CellsWithSpaces)
    End If
Application.ScreenUpdating = True
End Sub

1
Dzięki, działało to na adresy e-mail w dokumencie programu Excel - wypróbowałem kilka innych metod, ale to ta zadziałała, chociaż musiałem zmienić http: // na mailto:
bawpie

2
  1. Utwórz tymczasową nową kolumnę hiperłączy za pomocą formuły = HYPERLINK ()
  2. Skopiuj tę kolumnę do programu Microsoft Word (skopiuj do schowka tylko po uruchomieniu programu Word).
  3. Skopiuj wszystko z nowego dokumentu tekstowego (ctrl + a, a następnie ctrl + c).
  4. Wklej do Excela, zastępując oryginalną kolumnę tekstu. Usuń tymczasową kolumnę z formułą.

1

Możesz wstawić formułę =HYPERLINK(<your_cell>,<your_cell>)do sąsiedniej komórki i przeciągnąć ją do samego końca. To da ci kolumnę ze wszystkimi linkami. Teraz możesz wybrać oryginalną kolumnę, klikając nagłówek, kliknij prawym przyciskiem myszy i wybierz Hide.


1

Umieść adresy URL w tabeli HTML, załaduj stronę HTML do przeglądarki, skopiuj zawartość tej strony, wklej do Excela. W tym momencie adresy URL są zachowywane jako aktywne linki.

Rozwiązanie zostało zaproponowane na http://answers.microsoft.com/en-us/mac/forum/macoffice2008-macexcel/how-to-copy-and-paste-to-mac-excel-2008-a-list-of/ c5fa2890-acf5-461d-adb5-32480855e11e autor: (Jim Gordon Mac MVP) [ http://answers.microsoft.com/en-us/profile/75a2b744-a259-49bb-8eb1-7db61dae9e78]

Stwierdziłem, że zadziałało.

Miałem te adresy URL:

https://twitter.com/keeseter/status/578350771235872768/photo/1 https://instagram.com/p/ys5ASPCDEV/ https://igcdn-photos-ga.akamaihd.net/hphotos-ak-xfa1/t51 .2885-15 / 10881854_329617847240910_1814142151_n.jpg https://twitter.com/ranadotson/status/539485028712189952/photo/1 https://instagram.com/p/0OgdvyxMhW/ https://instagram.com/p/1nynTiiLSb/

Umieszczam je w pliku HTML (links.html) w następujący sposób:

<table>
<tr><td><a href="https://twitter.com/keeseter/status/578350771235872768/photo/1">https://twitter.com/keeseter/status/578350771235872768/photo/1</a></td></tr>
<tr><td><a href="https://instagram.com/p/ys5ASPCDEV/">https://instagram.com/p/ys5ASPCDEV/</a></td></tr>
<tr><td><a href="https://igcdn-photos-g-a.akamaihd.net/hphotos-ak-xfa1/t51.2885-15/10881854_329617847240910_1814142151_n.jpg">https://igcdn-photos-g-a.akamaihd.net/hphotos-ak-xfa1/t51.2885-15/10881854_329617847240910_1814142151_n.jpg</a></td></tr>
<tr><td><a href="https://twitter.com/ranadotson/status/539485028712189952/photo/1">https://twitter.com/ranadotson/status/539485028712189952/photo/1</a></td></tr>
<tr><td><a href="https://instagram.com/p/0OgdvyxMhW/">https://instagram.com/p/0OgdvyxMhW/</a></td></tr>
</table>

Następnie załadowałem links.html do mojej przeglądarki, skopiowałem, wkleiłem do Excela, a linki były aktywne.


1

Dziękuję Cassiopei za kod. Zmieniam jego kod, aby działał z lokalnymi adresami i wprowadziłem niewielkie zmiany w jego warunkach. Usunąłem następujące warunki:

  1. Zmień http:/nafile:///
  2. Usunięto wszystkie rodzaje białych znaków
  3. Zmieniono 10k zakres zasięgu komórki na 100k

Sub HyperAddForLocalLinks()
Dim CellsWithSpaces As String
    'Converts each text hyperlink selected into a working hyperlink
    Application.ScreenUpdating = False
    Dim NotPresent As Integer
    NotPresent = 0

    For Each xCell In Selection
        xCell.Formula = Trim(xCell.Formula)
            If InStr(xCell.Formula, "file:///") <> 0 Then
                Hyperstring = Trim(xCell.Formula)
            Else
                Hyperstring = "file:///" & Trim(xCell.Formula)
            End If

            ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=Hyperstring

        i = i + 1
        If i = 100000 Then Exit Sub
Nextxcell:
      Next xCell
    Application.ScreenUpdating = True
End Sub

Działając idealnie, utworzyłem ~ 7000 hiperłącza w zaledwie kilka sekund. :) Dziękuję Cassiopeia.
Junaid

dlaczego definiować NotPresent?
Steffen Roller

Właśnie skopiowałem powyższy kod z wcześniejszej odpowiedzi Cassiopei. I zmodyfikowałem to tak, jak to zrozumiałem. Więc nie wiem, czy NotPresent jest konieczne, czy nie. Ale mój zmodyfikowany kod działa doskonale.
Junaid,

1

Miałem listę liczb, które są podawane do adresu URL, który chcę połączyć z linkiem. Na przykład mam kolumnę A z numerami pytań (tj. 2595692, 135171) i chcę przekształcić te numery pytań w hotlinki i wyświetlić tylko numery pytań.

Zbudowałem więc hiperłącze tekstowe wskazujące na kolumnę A i skopiowałem je dla wszystkich numerów pytań:

= "= HYPERLINK (" & "" "http" "&" ":" "" & "" & "&" & "" "// stackoverflow.com/questions/"&A1&"""&&","&A1& „)”

Następnie kopiuję - wklejam wartość tej kolumny hiperłączy tekstowych do innej kolumny.

Otrzymasz kolumnę tekstu, która wygląda następująco:

= HYPERLINK („http” i „:” & ”// stackoverflow.com/questions/2595692",2595692)

Następnie wybrałem te wklejone elementy i uruchomiłem następujące makro F2Entry:

Sub F2Enter()
Dim cell As Range
Application.Calculation = xlCalculationManual
For Each cell In Selection
    cell.Activate
    cell = Trim(cell)
Next cell
Application.Calculation = xlCalculationAutomatic
EndSub

Następnie usunąłem kolumnę wprowadzania tekstu i kolumnę A.

Skończyłem z jedną kolumną z numerami pytań z linkiem:

2595692

135171

itp.

Twoje zdrowie


0

Jeśli skopiujesz treść tekstu do nowej kolumny i użyjesz:

=HYPERLINK("http://"&B10,B10) 

w oryginalnej kolumnie. Następnie użyj $dla kolumny, aby wyglądała następująco:

=HYPERLINK("http://"&$B10,$B10)

To jedyny sposób, w jaki działało dla mnie w programie Excel 2010 w systemie Windows 7. Możesz skopiować formułę.


0

Dla każdego, kto ląduje tutaj w programie Excel 2016, możesz po prostu podświetlić kolumnę, a następnie kliknąć Hyperlinkkartę znajdującą się na Homewstążce w Stylespolu.

wprowadź opis zdjęcia tutaj

Edycja: Niestety, to tylko aktualizuje styl komórki, a nie funkcję.


3
Daje to tylko style komórek, a nie klikalność adresu URL.
stenlytw

1
Tak, pomyślałem, że to świetne rozwiązanie, ale to nie działa. Naprawdę zaskakuje mnie, że to takie trudne.
Dan

Właśnie to przetestowałem, a wy dwaj macie rację; Oszukały mnie style komórek.
jGroot

@ jGroot - właśnie tego chciałem po prostu styl bez żadnej funkcjonalności.
Raj Rajeshwar Singh Rathore

-1

Najłatwiejszy sposób tutaj

  • Podświetl całą kolumnę
  • kliknij „wstaw”
  • kliknij „Hiperłącze”
  • kliknij „umieść w tym dokumencie”
  • Kliknij OK
  • to wszystko

To nie działa, chyba że link prowadzi do czegoś W TYM dokumencie.
Erica

Zgadzam się, że pytanie było źle sformułowane, ale zamierzonym celem jest wyraźnie cel samego tekstu.
Jason K.,

-3

Najprostszy sposób w programie Excel 2010: wybierz kolumnę z tekstem adresu URL, a następnie wybierz Styl hiperłącza z karty Strona główna. Wszystkie adresy URL w kolumnie są teraz hiperłączami.

Również dwukrotne kliknięcie każdej komórki na końcu tekstu adresu URL i dodanie pustego lub po prostu enter spowoduje również utworzenie hiperłącza. Podobne do sposobu tworzenia linków URL w wiadomościach e-mail MS Outlook.


4
Nie są hiperłączami, wyglądają jak hiperłącza. To tylko stylizacja.
Erica

-5

Jest na to bardzo prosty sposób. Utwórz jedno hiperłącze, a następnie użyj narzędzia Malarz formatów, aby skopiować formatowanie. Stworzy hiperłącze dla każdego elementu.


8
To nie działa, po prostu kopiuje formatowanie, aby wyglądały jak hiperłącza.
reergymerej
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.