Extract Text - Surely there's a way

Originally Posted by hmalone

Hi, I've been a basic user of excel for a while, and now I actually need to work out how to format/create a formual/sort for which I need some help.
I have a worksheet with 1,011 URLS in one colum, and the number of corresponding hits in another. The URLS look something like this

http://www.electives.net/en/1/whshos...TYPE=0&START=0

The numbers in the URLs are all slightly different but each one contains the following text
COUNTRY= and then a number after it
eg
COUNTRY=54
COUNTRY=121
COUNTRY=27

I need to be able to extract the the phrase COUNTRY=(and then number) and delete the rest of the text.

Can anyone out there help?
Am I destined to go completely mad as I go through each cell manually?

Thanks
Halina

there sure is:
=MID(A1,FIND("COUNTRY=",A1,1),FIND("CONTINENT=",A1,1)-FIND("COUNTRY=",A1,1)-1)

will give an error if continent= or country= aren't found

oh alright, well what you want to do is put that formula into a new cell. don't overwrite anything
so it's referencing cell A1. so that assumes that your URL is in cell A1
if that's the case you'd put that formula in the first blank cell right of A1, let's assume that's B1
now b1 will give you Country=54 (or whatever the correct answer is)
now copy b1 into all the cells below it until you've got as many formulas in column B as you have cells in A (since each formula calculates the country for one cell)

if your url is not in column A change A1 to wherever the first URL is. As long as the url's are listed in vertical order copying down the formula will update the formula to reference the new cell each time

hope that helps

ha no problem but now i'm going to scare you by explaining what it did

i know, i know, ignorance is bliss, but this is helpful i promise

=MID(A1,FIND("COUNTRY=",A1,1),FIND("CONTINENT=",A1,1)-FIND("COUNTRY=",A1,1)-1)

first mid
normal syntax is
=mid(Text,startnum,numchars)
so for example
=mid("Mark",2,2) would return "ar"
=mid("Mark",4,1) would return "k"
etc
only we've replace "Mark" with a cell reference to text

now find
normal syntax is
=find(find text,within text,start num)
find text is what you're looking for, in the first case "COUNTRY="
within text is again a cell reference to A1
start num is 1 because we want the first occurence.
so =find("COUNTRY=",A1,1) will return the character number where "COUNTRY=" begins.

now it gets a bit tricky. we need to know how many characters to return. now ideally your return strings would all be the same size, but you have some instances where the country= is a 2digit number and some where it's 3. as such we had to make a dynamic string size. What we did was we know the text we want always begins with "COUNTRY=" and ends with "CONTINENT=" so what we did was find the character number that "CONTINENT=" begins at and subtracted where "COUNTRY=" begins at.
the difference between these two is the text string shown.

voila, now that it makes sense it's hopefully not more intimidating than it was a minute ago