Hey I'm fairly new to excel and I am working on 2007. I need to have it look at column B and if it includes a certain phrase, have it edit column A based on that. So If column B says High School at all have column A say "H". Is this possible? If not, how else would I accomplish this without searching the huge file myself and typing in the appropriate letter. The reason I need this is I am exporting the excel stuff into a COBOL program for schools in a state, and the program needs to distinguish the type of school. For example, Wayne High School is different than Wayne Elementary school. The school name is what goes in Column B, and the letter for type of school in column A.
Last edited by Joe@WSC; 07-06-2009 at 04:25 PM.
You could try this, starting in row 1 and copy down:
=IF(ISERROR(SEARCH("*high school*",B1)),"","H")
Of course, this will only identify high schools. What all do you need to identify, and what codes do you need to use?
Jason
High School = H
Middle School = M
Junior High = J
Elementary = E
It can be a combination of any of them. If the name doesn't have the type of school in, then the code would just be HJME. In the COBOL program there is a scale saying k-12 is HJME and it defines each school as a range in the grades.
Could you explain what the iferror does too? I'm trying to learn as much as possible in a day. But thanks for your help!
Note: when using the SEARCH() function, the wildcards are implied and unneeded. If you were to use the FIND() function, the wildcards would actually give you an incorrect answer.
In either case, just "high school" is all that's needed in that parameter.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
A little beastly, but works:
=IF(ISNUMBER(SEARCH("elementary",B1)), "E", IF(ISNUMBER(SEARCH("junior",B1)), "J", IF(ISNUMBER(SEARCH("high",B1)), "H", IF(ISNUMBER(SEARCH("middle",B1)), "M", "HJME"))))
It's long, but it stops once it makes a match.
Last edited by JBeaucaire; 07-06-2009 at 03:41 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Utilising IFERROR, available in Excel 2007....
=IFERROR(LOOKUP(2^15,SEARCH({"High school","Middle School","Junior High","Elementary School"},B2),{"H","M","J","E"}),"HJME")
Ok, it worked! But, how do I get it to work down through the whole column. Sorry this one is probably a stupid question, but I appreciate the responses.
Deleted due to above posts.
**Off topic alert!**Originally Posted by JBeaucaire
Correct, JB. Theoretically there might be occasions where a wildcard could be useful within SEARCH, perhaps you want to search for the text string "xyz??123", where ?? might be any 2 characters.......but I've never yet found a practical use for it..........
**Back on topic**
To copy down the column, assume JB's formula in A1 then if you put the cursor on the bottom right corner of the cell you'll see a black +, this is the "fill-handle". If you double-click when you see it then formula will "fill down" as far as you have continous data in column B.
You can also manually drag. Again put cursor on bottom right but this time just left-click and hold down, now physically drag the formula down as far as needed, then release mouse. Obviously this second method isn't practical for very large ranges......
Last edited by daddylonglegs; 07-06-2009 at 03:57 PM.
DDL, your formula is just...well...beautimus. (Yes, that's a word, I wrote it into the margins of the dictionary).
Joe, I'd use that one just because it's so gorgeous!
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Ok thanks again for that fill down help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks