2

Generally speaking, I'm trying to create an MS Excel spreadsheet with hundreds of rows of related URLs. Column A should contain human-readable text, but the cell text is hyperlinked to an HTTP URL. Column B should contain a plaintext literal HTTP URL (this can or cannot be hyperlinked to its apparent URL, it doesn't matter). Column C and on have simple plaintext content. The spreadsheet would look like the following:

| Product A | http://host:1234/svn/product_a | other data ... |
| Product B | http://host:1234/svn/product_b | other data ... |
| Product C | http://host:1234/svn/product_c | other data ... |

Note the hyperlinked URL and the plaintext URL are related but different!

I currently have a bash+sed script generating this exhaustive list of repositories using simple regex substitution for the URLs, but I can't find any MS Excel docs on how to represent in plain-text a cell with hyperlinked text (ideally for copy-pasting into the spreadsheet).

If I were generating some sort of markup like HTML, it would be trivial; but unfortunately there are additional columns that need to be available for other non-developers to edit regularly.

Is there some way to create a Column A cell using just plaintext copied into the cell?

And sorry for the terrible title, I'm not at all familiar with MS Excel or its jargon

1 Answer 1

1

If column B already contains the URL, and column A contains your plaintext URL, then try this in cell C1. (This assumes that you can insert a column C for this use without messing up your organization. The other option is to type the plaintext name into each formula in column A.)

=HYPERLINK(B1,A1)

The parameters for HYPERLINK are (link_location,[friendly_name]) where friendly_name is optional.

UPDATE

NOTE: The following approach will only work if you want the friendly_name to be the same as the text after the last / in your URL.

Assuming your data starts in row 1, put this into cell A1 and drag it down.

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

Now, for the explanation!

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

SUBSTITUTE parameters are (text,old_text,new_text,[instance_num]) where instance_num is optional.

SUBSTITUTE replaces all of the / characters in the string with an empty string since we did not provide an instance_num parameter. With the LEN we get the length of the string minus the length of the string without the / which gives us the number of times that / occurs, which would be 4 for your OP.

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

In this case we are using the optional fourth parameter (I am using the number itself from the previous result here from the OP just for this example). This will only replace the last occurrence of / with a tag $, which should be some character that would not ever be part of your string. Your OP result would be "http://host:1234/svn$product_a"

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

FIND parameters are (find_text,within_text,[start_num]) and we don't need to use the optional start_num parameter since if we have pick the right tag there will only be one. (Once again, I am using the string from the previous result here from the OP just for this example).

The length of the string (30) minus the location of the tag (21) gives the length of the text after the last / (9).

RIGHT gives you the text string that are the rightmost 9 characters in the string.

IFERROR wraps this in an error check. If no error, you get the result of the calculation. If this errors, such as no / in the string for the FIND and SUBSTITUTE functions to work with, then it will return text in B1.

And finally, the HYPERLINK function will create a link from the URL in B1 with the friendly_name that results from the calculations above.

This way to find the last characters after the delimiter is not something I figured out, but is all over the place and I've used for so long that I don't know who to credit it to!

(Once I thought of this, I just had to add it :) Hope it helps.)

2
  • Thanks for the detail, demonstrates enough functionality that I think I can adapt it for my stuff. I'll update when I have results
    – ardnew
    Commented Sep 28, 2018 at 17:07
  • Yes, I would be curious to hear about your implementation.
    – Rey Juna
    Commented Sep 28, 2018 at 18:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .