delimiting text in Excel messes up hyperlinks

Question

I'm trying to paste a table that should never have been done in Word into Excel. Once in excel, there's a column with three separate sets of data that I wish to delimit into three columns. The third datum contain a hyperlink.

Delimiting using / works but the hyperlinks remain with the first datum rather the third. And i see that when I pasted from word, the entire contents of cells in this column became hyperlinked.

So: my question, is there a way to paste and keep the integrity of the hyperlinks. alternately, can i delimit and ensure that the links stay with the text/data in which they were originally inserted. I hope that makes sense.

Answer 1

I think I found a way... Let me know if this doesn't make sense, and I will try and do up screenshots. (I'm using Excel 2013 here but expect it will work in previous versions)

1) Go to the stage you have above, where when you did a "Text to Columns" you got the links on Columns 1, instead of Columns 3

2) Copy and paste Columns 1 --> Columns 4

3) Copy Columns 3

4) Paste Values (right click > Paste > Paste Special > Values ) onto Columns 4

5) Delete Columns 3

(If above doesn't work)

If this doesn't work then i would have a look at throwing together a quick macro to go break each of the delimited values into separate cells; then use something like what was answered below to copy the link to the new cell.

How do I copy hyperlink only (and not text) to another cell?