How to extract all the URLs from this list with excel?

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

Could you paste it into a worksheet?
We could, but we want to follow your desired layout.

is this what you desire as output?

I dont see any easy formula way to do this, that is a real mess. However, here is a bit of a work around that does work, but is going to take some work from you.

1. Copy/paste 5-5 rows of that data into excel (excel wont work with more than 8000~ characters, so keep that in mind)
2. repeat that until you have copied over all your data
3. Use Find/Replace with FIND http and REPLACE with &http
4 use Text2Columns using & as the delimiter

@ humding, how did you do that?

its not quite excel but here is what i did ...bear with me its wordy

open file in notepad or wordpad or whatever word processor you want

Find the timestamp information
in this case it was "daily2016-01-29T14:35:01+11:00"
find and replace with blank
then save File

open this file into excel and it should all be in cell
text to column using ":" delimiter
discard the first https and copy and paste special>>transpose

then find and replace "https" with blank

after which you can add "https:" & cell value to combine into the URL
copy and paste values and then bobs your uncle, done

ps need some minor touch up at the last entry

OK thanks, mine was kinda a messy work around as well

Originally Posted by humdingaling

in this case it was "daily2016-01-29T14:35:01+11:00"
find and replace with blank
then save File

Expanded issue:
What if it was not fixed?
It should be "2016-01-29" or "2016-01-30", if data is downloaded from 2 days?

OP can correct me if i am wrong

from what i can gather, this is a time stamp of when the document was downloaded
meaning it would be the same throughout the document in question

ok so i found a better way of doing it instead without the need to worry about the timestamp being consistent

open file in MS WORD
FIND "https:"
replace all with "^lhttps:"

^l = manual Line Break

what you end up with is a line break for every single URL (attached text file after doing this)

after which you can use a formula to delete everything after the last "/"

Formula:

Please Login or Register  to view this content.

Thx humdingaling

I try to open a blank word document, then open that txt file
Then copy/paste to excel.
It works for me, without replacing "http"

OK. I've learnt a lot from you, thx again.