Extracting alpha/numeric data from cells & recoding

slucan

New Member
Joined
Sep 1, 2007
Messages
2
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
For your first question:

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
7MADISON,WI537061490WI53706
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
Joined
Sep 1, 2007
Messages
2
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,818
Messages
5,598,280
Members
414,223
Latest member
Accountant2B

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top