Question:
what is the simplest way to extract text pattern in Excel spreadsheet?
yyjerseyian
2009-01-17 20:34:54 UTC
I have a sheet of a few columns of URLs. I need to extract a text pattern (I.E. extracting an URL parameter) and then replace it with something else and place it into another link structure. What's the simplest way to do that in Microsoft Excel spreadsheet? Does Excel provide regular express?
Four answers:
VBAXLMan
2009-01-17 22:43:49 UTC
Yes, sure

I do this all the time

Now, the link you have may look like this

http://www.file1.net/project.asp?id=89



If this link in cell A1, then paste this in B1

=LEFT(A1, SEARCH("?",A1)-1)



This will bring you the first part until the "?"



Then in another cell, say C1 type 5



Then in D1 paste this

=HYPERLINK( B1&"id="&C1, B1)



Now this link will have this result

http://www.file1.net/project.asp?id=5



So doing 6 in C2, 7 in C3, etc

Copy and paste D1 down to generate multiple hyperlinks to different locations based on your criteria



I did this as example, because you didn't give any details



try applying your criteria and see



VBAXLMan is here to feed your Excel needs in case you want more help
?
2016-12-09 00:54:29 UTC
Extract Url From Hyperlink Excel
James
2009-01-17 20:41:51 UTC
No regular expressions but you do have some options. Say your parameter is n. I would make a couple of "intermediate" columns that include the position of n= (look into the FIND or SEARCH function), then use LEFT RIGHT or MID to extract the pieces you need (for example, get everything to the right of n=, then look for & and get everything to the left of the & in the new string). Look in the help under string functions for more info.
Michael E
2009-01-17 21:17:40 UTC
You could use text to columns with a "/" delimiter.

If you are on a Windows version, Excel VBA supports regular expressions.



If you have examples of inputs and desired outputs, the free formus at



http://www.mrexcel.com

http://www.excelforum.com



have some helpful folks.


This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.
Loading...