Hi guys,
I have a database export in a spreadsheet. I would like to search a cell of text and extract any pdf links, so anything that ends in pdf. I have played around with FIND and LOOKUP functions but without any luck.
Any help would be greatly appreciated. Should be a relatively easy one but not for me!
Can you give an example? Before and after?
Before:
A1
the quick brown fox this/is/a.pdf
C1 - Result field
Empty
After
C1
this/is/a.pdf
Possibly:
This is an array formula so should be entered with control+shift+enterPHP Code:=MID(A1,MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=" ")*ROW(INDIRECT("A1:A"&LEN(A1))))+1,LEN(A1))
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
Does that mean there will only ever be one mention of pdf per cell? If not, please provide a better data sample.
Kyle, that will only extract the text after the last space. If there is text after the PDF link, that won't work.
Mundo, please provide a data sample that represents all the possible variations of how often and where in the text your PDF link can occur.
cheers,
hmm, thanks Teylyn, hadn't thought about that....
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
PHP Code:<p>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. <a href="/webadmin/folder/folder/test.pdf">Download the pdf</a>It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p>
hmm, that doesn't look like a database export, it looks like a web page
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
It's an export from the database of a website.
ok, try this, this will only return one pdf link (the first)
PHP Code:=MID(A1,FIND("""/",A1)+1,FIND(""">",A1)-FIND("""/",A1)-1)
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
Thanks Kyle123
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks