# Extracting alpha/numeric data from cells & recoding

#### slucan

##### New Member
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. ALBUQUERQUE, NM 87131-5041 , 871315041
b. PROVIDENCE, RI 02912

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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### facethegod

##### Well-known Member

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

Code:
``````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
ABCD
1MD,MS,BA1
2PHD,MS,MPH2
3DO,PHD,MBA,MA3
4MS,OMD4
5ALBUQUERQUE,NM87131-5041,871315041NM87131
6PROVIDENCE,RI02912RI02912
8
9
Sheet1

Formula in A1-A4

Code:
``=MyDegrees(A1), copied down``

2nd Question:

Formula in B5,C5 respectively copied down

Code:
``=MID(A5,FIND(" ",A5)+1,2)``

Code:
``=MID(A5,FIND(B5,A5)+3,5)``

Good Luck :wink:

#### slucan

##### New Member
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.

Thanks,

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,025
Messages
5,835,008
Members
430,332
Latest member
Charly_Moon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back