I have been given a list of 2000 some-odd web URLs, which contain the names of the schools to which they belong. My task is to type the full name of the School into the adjacent column. Doing this manually will take hours upon hours, and I know Excel has the power to perform many different actions. So, my question is this: is there a function that will search for a specific text in column X and, if the specified text is found, place another specified text the adjacent cell of column Y?
Example? Do you mean you want to take out the "www. .edu" and only write "utexas" on the right column? Is that it??
Well, OK... output would be different, but... where do you get the names of the schools? From the right cell that contains the URL? If this is the case, I could think of one way in Linux using wget to retrieve the TITLE tags of the URL of the school and getting the name. OK, so you posted the example... yeah, you can get a substring before the .edu and to the next dot. For example: something.somethingelse.university.edu It can write "university.edu" on the right with the right "replace", "search", "substitute" or "right" method. It seems to me you're not even trying, man. :grin:
I know the names of the schools. With "Find" I can search for all the instances of blanksville.ms and then paste the name in to other column. I was just wondering if there was a way to have the program do it. You know, if it finds 45 cells in column D that contain blanksville.ms It will then place Blanksville Middle School into the corresponding cell in Column B.
I'm no programmer. I just thought I'd find out if it was somthing braindead easy that I just had no idea how to.
If there are multiple possibilities and it's not very deterministic, it could be very difficult from a programming perspective. If all URL's are of the form www.*.ms.edu and you just want to pluck out the *, that isn't difficult. But all you'll be able to do it pluck out the * from the URL. In the utexas.edu example, a program wouldn't know if "utexas" is "University of Texas" or not unless you told it explicitly. And if you're doing that, you might as well just type them all out yourself.
I'm sure Excel can help you out some though. You could do a find/replace to delete the www. and the .edu. Then, you still need to turn utexas into University of Texas, but Yale would already be done.
Check this website to see if he has anything that might help. http://www.cpearson.com/excel/mainpage.aspx I learned how to turn "FirstName LastName" in one column into two columns for each value. (helpful for mail merge). If there is nothing there that will do it, check out ASAP Utilities. Free download and it might be able to crack what you are trying to do.
See if this helps: if you have www.universityoftexas.com in Cell A1.. you can do this.. type in B2.. right(A1, len(A1)-4). Then, type in C3.. left(A2, len(A2)-4). Then 'Hide column B, and you'll be left with 'universityoftexas' next to the website.
edit: ignore cell numbers above.. use this instead: you can do this.. type in B1.. right(A1, len(A1)-4). Then, type in C1.. left(B1, len(B1)-4). Then 'Hide column B, and you'll be left with 'universityoftexas' next to the website.
