IF function excluding 0 and 9 in text string

S

Seph

New Member
Joined
Jan 28, 2012
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
I have 4 Parole Agents that are assigned cases according to inmate numbers. The inmate numbers are always two letters followed by 4 numbers. For instance, PE1012.

Agent1 has inmate numbers ending in 1 and 2
Agent2 has inmate numbers ending in 3 and 4
Agent3 has inmate numbers ending in 5 and 6
Agent4 has inmate numbers ending in 7 and 8

If the inmate# ends in 0 or 9 then they are assigned by the preceding whole number. For instance, PE1099 would be Agent1’s case based off the 1. PE7930 would be Agent4’s case based off the 3 and so forth.

I’ve gotten this far:

=IF($C8<="","",IF($C8="","",CHOOSE(INT((RIGHT(SUBSTITUTE($C8,"0",""))-1)/2)+1,"Wright","Prenatt","Dzara","Mott")))

C8 is the inmate#.

I can account for the 0, but I’m not sure what to do with the 9.

Book1.xlsx
ABCDEFGHIJ
7LOCParole NumberInst NumberOffender NameMinimum DateInterview TypeMajority/Panel/ RRRIRRRIReason Not SeenAGENT
8929KK PE1100ARMENTROUT-LOPEZ MARLIESSA 08-01-2024ERWright
9508ID PE0902BLAKELEY BRENDA L 07-13-2022VMWright
10308KS PE6925BOAS CODY BERNICE 08-24-2024MPDzara
11122JK PE6829DENNIS TIFFNAY 06-23-2024MP#VALUE!
12935KK PC3813FENTON ANGEL LEE 08-02-2024MMPrenatt
13207KR PE6812FULLEM LEAH 11-09-2023ERWright
14365JF PC1987GEELEN ELIZABETH 01-19-2020VPMott
15015FN PE6407GROGAN ERIKA MICHELE 08-30-2024MPMott
16995JQ PD6424HARRINGTON LATRICE MARIE 08-25-2024MPPrenatt
17812JW PB2462HENCHELL ASHLEY 08-15-2022RMWright
18808KU PE7161HODOWANES ASHLEY L 06-24-2024MPWright
19788KU PE7157HOSSLER KATHARINE ALICE 02-27-2024ERMott
20163JQ PB4309KELLEY JOANNA 08-26-2021VP#VALUE!
21738HF PE5538LAMP DESIREA MARIE 07-09-2023RPMott
22852GV PA5936LEHMAN ANGELA 08-01-2024MPDzara
23858KE PB0451MCLAUGHLIN CHAUNITA 08-30-2023RMWright
24369KU PE7116MERK KRISTEN AMBER 05-30-2024JRDzara
252001I PE6820MILLER ALICE 08-11-2024JRWright
26238KS PE6923MORGAN KAREN LEE 08-25-2024JPPrenatt
27779KU PE7152MORGAN KIMBERLY A 06-11-2024MMWright
28409KJ PE6383NAVARRO ARIEL 06-25-2023RMPrenatt
29274KH PE6240NYIRI MEDINA 08-08-2024JPPrenatt
30940KS PE6986OZMORE SHERRY DAWN 08-09-2024MPDzara
31281KE PE5951PIERCE BRANDY SUE 11-24-2023RMWright
32363KR PE3972POLISKY DIANE 08-05-2024ERWright
33235JH PC9835PRITTS BRENDA ROSE 08-27-2024MMDzara
34187KT PE7010REBMAN CARLY E 08-16-2024ERWright
35950IL OY9658RUSH CRYSTAL LASHEA 09-09-2018SMMott
36961IW OZ5316SHAFFER CHRISTINA RUTH 02-08-2019VPDzara
37549GL PE6981SMITH PATRICIA M 08-12-2024MPWright
38861KE PD2275STRICKLAND TIARAH 08-01-2023RPDzara
39713IA PC8788WILLIAMS LISA MARIE 03-26-2020SPMott
40288JX OX9592WOODHOUSE SHADE 09-24-2022RMWright
41416KF PE6050WOOLF DASIA A. 10-25-2023RMDzara
42981KE PE6002YOUNG SHAVON 08-25-2023RMWright
Sheet1
Cell Formulas
RangeFormula
J8:J42J8=IF($C8<="","",IF($C8="","",CHOOSE(INT((RIGHT(SUBSTITUTE($C8,"0",""))-1)/2)+1,"Wright","Prenatt","Dzara","Mott")))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A8:I42Expression=$A8="O"textYES
A8:I42Expression=$A8="X"textYES
G8:G42Expression=$H8="Y"textNO
G8:G42Cell Valuecontains "N"textNO
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
If that formula is working for you for everything except 9, then make this adjustment:

=IF($C8<="","",IF($C8="","",CHOOSE(INT((RIGHT(SUBSTITUTE(SUBSTITUTE($C8,"9",""),"0",""))-1)/2)+1,"Wright","Prenatt","Dzara","Mott")))

Replace the $C# in the first SUBSTITUTE with the bolded portion above.
 
Upvote 1
Solution
Hi Seph

Mappe6
ABCDEFGHIJ
7LOCParole NumberInst NumberOffender NameMinimum DateInterview TypeMajority/Panel/ RRRIRRRIReason Not SeenAGENT
8929KK PE1100ARMENTROUT-LOPEZ MARLIESSA 01.08.2024ERWright
9508ID PE0902BLAKELEY BRENDA L 13.07.2022VMWright
10308KS PE6925BOAS CODY BERNICE 24.08.2024MPDzara
11122JK PE6829DENNIS TIFFNAY 03.06.2024MPWright
12935KK PC3813FENTON ANGEL LEE 02.08.2024MMPrenatt
Tabelle1
Cell Formulas
RangeFormula
J8:J12J8=INDEX(tbl_Agents[Agent],MATCH(--RIGHT(RIGHT(SUBSTITUTE(SUBSTITUTE([@[Inst Number]],0,""),9,""))),tbl_Agents[Last digit],0))


Mappe6
DE
1AgentLast digit
2Wright1
3Wright2
4Prenatt3
5Prenatt4
6Dzara5
7Dzara6
8Mott7
9Mott8
Tabelle2
 
Upvote 0
dreid1011 said:
If that formula is working for you for everything except 9, then make this adjustment:

=IF($C8<="","",IF($C8="","",CHOOSE(INT((RIGHT(SUBSTITUTE(SUBSTITUTE($C8,"9",""),"0",""))-1)/2)+1,"Wright","Prenatt","Dzara","Mott")))

Replace the $C# in the first SUBSTITUTE with the bolded portion above.
Click to expand...

I was so close!! Thanks dreid1011.
 
Upvote 0
You must log in or register to reply here.

Similar threads

welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
88
welshgasman
welshgasman
M
  • Solved
Formula Help.
Replies
3
Views
437
Fluff
Fluff
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
804
felixstraube
F
airforceone
  • Solved
Populate Rows & Columns by Comma Split (DYNAMIC VERSION ver.2)
Replies
4
Views
201
airforceone
airforceone
V
  • Question Question
Hours functions
Replies
4
Views
565
jkpieterse
jkpieterse

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

Share this page

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
Back
Top