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.
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.