loading

use excel to strip city name from team name in NFL?

Hi all,

how would i go about stripping the city from a list (over 3000) of Team names.

ie Arizona Cardinals -> Cardinals

i have a spreadsheet listing all games from Sunshine Forecast for the 1978 - 2011 seasons and have a lookup so i can see the matchups between any team and i will show me the Score, winner, loser & Home/Road.

but the teams have moved/ changed names in that time.

sort by: active | newest | oldest
frollard4 years ago
It's a bit convoluted but the quickest I can think of:

1) SAVE A COPY OF THIS SPREADSHEET SOMEWHERE, THIS ACTION CAN ACCIDENTALLY WRECK YOUR SHEET AND YOU MAY BE VERY SAD IF YOU TYPO AND LOSE SOMETHING IMPORTANT!

Form a list of all the words that are 'blacklisted' that you want rid of.

use ctrl+f (find) then go to the replace tab.

Type in the word you want rid of, like "Arizona " (no actual quotes, note the extra spacebar in there)
in the replace with, leave it blank "", hit replace all.
then change "Arizona" to "Arizona" (note the no space after)
hit replace all (still with blank)

This will remove all instances of "Arizona " and "Arizona" -- may or may not be necessary based on if the text is all formatted properly and or needs punctuation added or removed.

Be careful removing words that may be part of other words as the algorithm doesn't care if it's inside a word.
"You youtube user you." if you remove "you" becomes
" tube user " (note extra spaces and mid-words removed)
if you remove "you " (with space) it becomes:
"youtube user you."

per rule 1 above, be careful replacing something too simple like " " (single space) or individual letters or diphthongs as it can really destroy a lot of words where you don't want. Stick to big words until you get the hang of it.
There is another method that works only if the data is all formatted properly: if all the names are in one column and it is always
"Cityname[space]teamname" with no extra spaces, you can use
'text to columns' with a spacebar delimiter, this will pull all the citynames into one column and all the teamnames into the second column. This can be handy if you want to keep the data (knowing which city the team was in when it did a certain thing, but you can still filter and sort and act on just the teamname.

If that won't do, you can write a formula that says "give me all the text to the left or right of a particular character number - and that character number is the first instance of a found space character" I would have to look it up but it's something like =Left(Cellname,instr(cellname," "),len(Cellname-instr(cellname," ")
give me the characters at the left of the cellname for the length of 'all the cell's length minus where you first found a spacebar"

It's really convoluted but can be handy to write one function like that sometimes.
furby (author)  frollard4 years ago
the problem with both methods is Two Word city names.
Eg. Green Bay Packers
frollard4 years ago
@furby
If it's not too difficult to know off the bat, and you have a knowledge of the multi-word names, you can replace 'green bay packers' with 'green_bay packers', etc.
Jayefuu4 years ago
You need to read a guide on regular expressions (RegEx) for Excel. We can use this with search and replace to selectively delete bits.

The regular expression you need will depend on the layout, ie are any of the team names two parts, are any of the town name 3 parts. We can write one that will strip by spaces and count the spaces.

If you upload the file to your original post, I'd be happy to look at it to do this for you.

James