Создайте много ячеек с гиперссылками с читаемым текстовым содержимым вместо литерального URL

Question

Вообще говоря, я пытаюсь создать электронную таблицу MS Excel с сотнями строк связанных URL-адресов. Column A должен содержать читабельный текст, но текст ячейки содержит гиперссылку на HTTP-URL. Column B должен содержать обычный текстовый URL-адрес HTTP (это может или не может быть связано с его очевидным URL, это не имеет значения). Column C и далее имеет простой текстовый контент. Электронная таблица будет выглядеть следующим образом:

| Продукт А | http:// хост:1234/ svn/ product_a | другие данные ... |
| Продукт Б | http:// хост:1234/ svn/ product_b | другие данные ... |
| Продукт C | http:// хост:1234/ svn/ product_c | другие данные ... |

Обратите внимание, что гиперссылка на URL-адрес и URL-адрес в виде простого текста связаны, но различны!

В настоящее время у меня есть сценарий bash+sed генерирующий этот исчерпывающий список репозиториев с использованием простой замены регулярных выражений для URL-адресов, но я не могу найти никаких документов MS Excel о том, как представлять в виде обычного текста ячейку с гиперссылочным текстом (идеально для копирования вставка в электронную таблицу).

Если бы я генерировал какую-то разметку, такую как HTML, это было бы тривиально; но, к сожалению, есть дополнительные столбцы, которые должны быть доступны для других не-разработчиков для регулярного редактирования.

Есть ли какой-нибудь способ создать ячейку Column A используя только открытый текст, скопированный в ячейку?

И извините за ужасный заголовок, я совсем не знаком с MS Excel или его жаргоном

Answer 1

Если столбец B уже содержит URL-адрес, а столбец A содержит URL-адрес в виде обычного текста, попробуйте сделать это в ячейке C1 . (Это предполагает, что вы можете вставить столбец C для этого использования, не портя организацию. Другим вариантом является ввод имени открытого текста в каждую формулу в столбце А.)

=HYPERLINK(B1,A1)

Параметры для HYPERLINK : (link_location,[friendly_name]) где friendly_name не является обязательным.

ОБНОВИТЬ

ПРИМЕЧАНИЕ. Следующий подход будет работать только в том случае, если вы хотите, чтобы friendly_name было таким же, как текст после последнего / в вашем URL.

Предполагая, что ваши данные начинаются со строки 1, поместите их в ячейку A1 и перетащите их вниз.

=HYPERLINK(B1,IFERROR(RIGHT(B1,LEN(B1)-FIND("$",SUBSTITUTE(B1,"/","$",LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))))),B1))

Теперь для объяснения!

LEN(B1)-LEN(SUBSTITUTE(B1,"/","")

SUBSTITUTE параметры (text,old_text,new_text,[instance_num]) , где instance_num не является обязательным.

SUBSTITUTE заменяет все символы / в строке пустой строкой, поскольку мы не предоставили параметр instance_num. С LEN мы получаем длину строки минус длина строки без / что дает нам число раз, которое / происходит, что будет 4 для вашего OP.

SUBSTITUTE(B1,"/","$",4)

В этом случае мы используем необязательный четвертый параметр (я использую само число из предыдущего результата здесь из OP только для этого примера). Это заменит только последнее вхождение / на тег $ , который должен быть каким-то символом, который никогда не будет частью вашей строки. Ваш результат OP будет " http://host:1234/svn $ product_a"

LEN(B1)-FIND("$","http://host:1234/svn$product_a") 21

Параметры FIND : (find_text,within_text,[start_num]) и нам не нужно использовать необязательный параметр start_num, поскольку, если мы выберем правильный тег, будет только один. (Еще раз, я использую строку из предыдущего результата здесь из OP только для этого примера).

Длина строки (30) минус расположение тега (21) дает длину текста после последнего / (9).

RIGHT дает вам текстовую строку, которые являются самыми правыми 9 символами в строке.

IFERROR оборачивает это в проверку ошибок. Если ошибок нет, вы получите результат расчета. Если это ошибки, такие как отсутствие / в строке для функций FIND и SUBSTITUTE для работы, то он вернет текст в B1 .

И, наконец, функция HYPERLINK создаст ссылку из URL-адреса в B1 с friendly_name, полученного в результате вычислений выше.

Этот способ найти последние символы после разделителя - это не то, что я понял, а повсеместно, и я так долго пользовался, что даже не знаю, кому это нужно!

(Как только я подумал об этом, мне просто нужно было добавить его :) Надеюсь, это поможет.)