Extracting alpha/numeric data from cells & recoding

[slucan]

I am new to working with Excel for data management. I found some useful tips in the Help files but couldn't put it all together to get the results I need.

Basically, I would like to extract data and recode.

Example data from cells in a column on professional degrees:
a. MD, MS, BA
b. PHD, MS, MPH
c. DO, PHD, MBA, MA
d. MS, OMD

I would like to recode as:
if ("MD" OR "DO") AND NOT ("PHD" OR SCD") then 1,
if ("PHD" OR "SCD") AND NOT ("MD" or DO") then 2,
if ("MD" OR "DO") AND ("PHD" OR SCD") then 3,
if NOT ("MD" OR "DO" OR "PHD" OR SCD") then 4.
* Varying position of degrees in lists within cells complicates this


A related problem involves addresses:
a. ALBUQUERQUE, NM 87131-5041 , 871315041
b. PROVIDENCE, RI 02912
c. MADISON, WI 537061490

I would like to make 2 new columns for just:
1. 2 letter state abbreviation
2. 5-digit zip code.
* varying position of information and redundancy of zip codes within cells complicates this. If I could somehow write code to recognize the 2-digit State abbreviation and then the 6 digits (space + 5-digit zip code) that follows, I would be set.

Any tips?
Thanks.

 

[facethegod]

For your first question:

1. Press Alt+F11
2. Insert|Module, Then Copy and paste this code into the code window

Option Explicit
Function MyDegrees(rng As Range) As String

Dim Ar, Ar1, x, i As Variant
On Error Resume Next
Ar = Array("", "", "MD", "DO", "PHD", "SCD")
Ar1 = Split(rng, ",")
For i = LBound(Ar1) To UBound(Ar1)
x = x + Application.Match(Ar1(i), Ar, 0)
Next
Select Case x
Case 3, 4
    MyDegrees = 1
Case 5, 6
    MyDegrees = 2
Case 8 To 10
    MyDegrees = 3
Case Else
    MyDegrees = 4
End Select
End Function

3. Press Alt + Q

Book1
	A	B	C	D
1	MD,MS,BA	1
2	PHD,MS,MPH	2
3	DO,PHD,MBA,MA	3
4	MS,OMD	4
5	ALBUQUERQUE,NM87131-5041,871315041	NM	87131
6	PROVIDENCE,RI02912	RI	02912
7	MADISON,WI537061490	WI	53706
8
9
Sheet1

Formula in A1-A4

=MyDegrees(A1), copied down

2nd Question:

Formula in B5,C5 respectively copied down

=MID(A5,FIND(" ",A5)+1,2)

=MID(A5,FIND(B5,A5)+3,5)

Good Luck :wink:

 

[slucan]

Many Thanks. You provided good fish, but unfortunately my exact scenario is a littel different and I couldn't figure out how to fish.

For question one:
- After inserting the modlue code, I couldn't get the caluculations to work.
pasting "=MyDegrees(A1), copied down" --> "#VALUE!" and pasting "=MyDegrees(AX)" --> "4" regardless of whether "X" was input as 1,2,3, or 4. (?)
- Also, I simplified the example to illustrate the case. Actually, I would like MyDegrees=2 to include PHD, DSC, SCD, EDD, and DRPH (and MyDegrees=3 to follow--i.e. equal MyDegreees 1+2) I don't understand the code for MyDegrees=3, or how to make the necessary adjustments.

For Question two.
I understand the code for zip code. But I can't get the code for State to work because again, I oversimplified. The data actually looks like this:

a. CASE WESTERN RESERVE UNIV 10900 EUCLID AVENUE CLEVELAND, OH 44106-7015 , 441067015

b. UNIVERSITY OF WISCONSIN RESEARCH & SPONSORED PROGRAMS 750 UNIVERSITY AVE, 4TH FL. MADISON, WI 53706-1490 MADISON, WI

c. UNIVERSITY OF MINNESOTA SPONSORED PROJECTS ADMIN 200 OAK STREET SOUTHEAST UNIVERSITY GATEWAY, SUITE 450 MINNEAPOLIS, MN 554552070

And come to think of it, I probably want a column for city as well.

Any advice?
Thanks,