conditions on a 5-digit number

S

Sara Padidar

New Member
Joined
Jul 3, 2024
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
I have a problem in excel, I would be grateful if you can help me. The subject is about a 5-digit number(ABCDE) that I should name it according to it's ease to remember. My main problem is that I don't know how to refer for example to the second char in the number or the second and third chars succesively(ex: BE). The main idea is to write IF function and nest some AND/OR functions in it. Is it true?
pattern.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
I think the function you are thinking of is MID, MID allows you to refer to the Nth letter/ number of a string. See below for an example:
Book1
ABCDEFG
27111171111Diamond
37711177111Diamond
44567845678Diamond
57807878078Diamond
67817878178Gold
77773277732Gold
87960079600Silver
97165471654Bronze
101212112121Ordinary
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=--MID($A2,1,1)
C2:C10C2=--MID($A2,2,1)
D2:D10D2=--MID($A2,3,1)
E2:E10E2=--MID($A2,4,1)
F2:F10F2=--MID($A2,5,1)
G2:G10G2=IF(OR(AND(C2=D2,D2=E2,E2=F2),AND(B2=C2,D2=E2,E2=F2),AND(C2=B2+1,D2=C2+1,E2=D2+1,F2=E2+1),AND(B2&C2=E2&F2,D2=0)),"Diamond", IF(OR(AND(B2=E2,C2=F2,D2<>0),AND(B2=C2,C2=D2,F2<>E2,E2<>B2)),"Gold", IF(E2&F2="00","Silver", IF(AND(E2=D2-1,F2=E2-1),"Bronze", "Ordinary" ))))
 
Upvote 0
Solution
Welcome to the MrExcel forum!

You can use text functions like LEFT, RIGHT, MID, REPT, etc. on numeric values. The result will be a text value, even if the text value is a digit. So you could do something like this:

Book1
ABC
1example (5 digit number)conditionPattern Value
271111B=C=D=EDiamond
377111A=B & C=D=EDiamond
445678B=A+1 & C=B+1 & D=C+1 & E=D+1Diamond
578078AB=DE & C=0Diamond
678178A=D & B=E & C<>0Gold
777732A=B=C & E<>D<>AGold
879600DE=00Silver
971654D=C-1 & E=D-1Bronze
1017390Otherordinary
Sheet7
Cell Formulas
RangeFormula
C2:C10C2=IF(OR(RIGHT(A2,4)=REPT(RIGHT(A2),4),A2&""=REPT(LEFT(A2),2)&REPT(RIGHT(A2),3),AND((MID(A2,{1,2,3,4},1)+1)&""=MID(A2,{2,3,4,5},1)),A2&""=LEFT(A2,2)&0&LEFT(A2,2)),"Diamond",IF(OR(AND(LEFT(A2,2)=RIGHT(A2,2),MID(A2,3,1)<>"0"),AND(LEFT(A2)=MID(A2,{2,3},1),LEFT(A2)<>MID(A2,{4,5},1),MID(A2,4,1)<>RIGHT(A2))),"Gold",IF(RIGHT(A2,2)="00","Silver",IF(AND(MID(A2,{4,5},1)=(MID(A2,{3,4},1)-1)&""),"Bronze","ordinary"))))


Using helper columns like Georgiboy did makes for a shorter formula.
 
Upvote 0
There are some overlapping conditions such as B2 and B8. An example would be 70000 where both conditions are met. When this happens, which category does it belong to?
This might not be the only one (I haven't checked).
 
Upvote 0
Like this:

Book1
ABC
1example (5 digit number)conditionPattern Value
271111B=C=D=EDiamond
377111A=B & C=D=EDiamond
445678B=A+1 & C=B+1 & D=C+1 & E=D+1Diamond
578078AB=DE & C=0Diamond
678178A=D & B=E & C<>0Gold
777732A=B=C & E<>D<>AGold
879600DE=00Silver
971654D=C-1 & E=D-1Bronze
1017390Otherordinary
1177771??
Sheet1
Cell Formulas
RangeFormula
C2:C10C2=IF(OR(MOD(RIGHT(A2,4),1111)=0,MOD(RIGHT(A2,3),111)+MOD(LEFT(A2,2),11)=0,MOD(A2,11111)=1234,A2=LEFT(A2,2)*1001),"Diamond",IF(OR(LEFT(A2,2)=RIGHT(A2,2),MOD(LEFT(A2,3),111)=0),"Gold",IF(MOD(A2,100)=0,"Silver",IF(MOD(RIGHT(A2,3),111)=99,"Bronze","ordinary"))))
 
Upvote 0
Eric W said:
Welcome to the MrExcel forum!

You can use text functions like LEFT, RIGHT, MID, REPT, etc. on numeric values. The result will be a text value, even if the text value is a digit. So you could do something like this:

Book1
ABC
1example (5 digit number)conditionPattern Value
271111B=C=D=EDiamond
377111A=B & C=D=EDiamond
445678B=A+1 & C=B+1 & D=C+1 & E=D+1Diamond
578078AB=DE & C=0Diamond
678178A=D & B=E & C<>0Gold
777732A=B=C & E<>D<>AGold
879600DE=00Silver
971654D=C-1 & E=D-1Bronze
1017390Otherordinary
Sheet7
Cell Formulas
RangeFormula
C2:C10C2=IF(OR(RIGHT(A2,4)=REPT(RIGHT(A2),4),A2&""=REPT(LEFT(A2),2)&REPT(RIGHT(A2),3),AND((MID(A2,{1,2,3,4},1)+1)&""=MID(A2,{2,3,4,5},1)),A2&""=LEFT(A2,2)&0&LEFT(A2,2)),"Diamond",IF(OR(AND(LEFT(A2,2)=RIGHT(A2,2),MID(A2,3,1)<>"0"),AND(LEFT(A2)=MID(A2,{2,3},1),LEFT(A2)<>MID(A2,{4,5},1),MID(A2,4,1)<>RIGHT(A2))),"Gold",IF(RIGHT(A2,2)="00","Silver",IF(AND(MID(A2,{4,5},1)=(MID(A2,{3,4},1)-1)&""),"Bronze","ordinary"))))


Using helper columns like Georgiboy did makes for a shorter formula.
Click to expand...
thanks a million
 
Upvote 0
Phuoc said:
Like this:

Book1
ABC
1example (5 digit number)conditionPattern Value
271111B=C=D=EDiamond
377111A=B & C=D=EDiamond
445678B=A+1 & C=B+1 & D=C+1 & E=D+1Diamond
578078AB=DE & C=0Diamond
678178A=D & B=E & C<>0Gold
777732A=B=C & E<>D<>AGold
879600DE=00Silver
971654D=C-1 & E=D-1Bronze
1017390Otherordinary
1177771??
Sheet1
Cell Formulas
RangeFormula
C2:C10C2=IF(OR(MOD(RIGHT(A2,4),1111)=0,MOD(RIGHT(A2,3),111)+MOD(LEFT(A2,2),11)=0,MOD(A2,11111)=1234,A2=LEFT(A2,2)*1001),"Diamond",IF(OR(LEFT(A2,2)=RIGHT(A2,2),MOD(LEFT(A2,3),111)=0),"Gold",IF(MOD(A2,100)=0,"Silver",IF(MOD(RIGHT(A2,3),111)=99,"Bronze","ordinary"))))
Click to expand...
thank you so much
 
Upvote 0
Georgiboy said:
I think the function you are thinking of is MID, MID allows you to refer to the Nth letter/ number of a string. See below for an example:
Book1
ABCDEFG
27111171111Diamond
37711177111Diamond
44567845678Diamond
57807878078Diamond
67817878178Gold
77773277732Gold
87960079600Silver
97165471654Bronze
101212112121Ordinary
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=--MID($A2,1,1)
C2:C10C2=--MID($A2,2,1)
D2:D10D2=--MID($A2,3,1)
E2:E10E2=--MID($A2,4,1)
F2:F10F2=--MID($A2,5,1)
G2:G10G2=IF(OR(AND(C2=D2,D2=E2,E2=F2),AND(B2=C2,D2=E2,E2=F2),AND(C2=B2+1,D2=C2+1,E2=D2+1,F2=E2+1),AND(B2&C2=E2&F2,D2=0)),"Diamond", IF(OR(AND(B2=E2,C2=F2,D2<>0),AND(B2=C2,C2=D2,F2<>E2,E2<>B2)),"Gold", IF(E2&F2="00","Silver", IF(AND(E2=D2-1,F2=E2-1),"Bronze", "Ordinary" ))))
th
Georgiboy said:
I think the function you are thinking of is MID, MID allows you to refer to the Nth letter/ number of a string. See below for an example:
Book1
ABCDEFG
27111171111Diamond
37711177111Diamond
44567845678Diamond
57807878078Diamond
67817878178Gold
77773277732Gold
87960079600Silver
97165471654Bronze
101212112121Ordinary
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=--MID($A2,1,1)
C2:C10C2=--MID($A2,2,1)
D2:D10D2=--MID($A2,3,1)
E2:E10E2=--MID($A2,4,1)
F2:F10F2=--MID($A2,5,1)
G2:G10G2=IF(OR(AND(C2=D2,D2=E2,E2=F2),AND(B2=C2,D2=E2,E2=F2),AND(C2=B2+1,D2=C2+1,E2=D2+1,F2=E2+1),AND(B2&C2=E2&F2,D2=0)),"Diamond", IF(OR(AND(B2=E2,C2=F2,D2<>0),AND(B2=C2,C2=D2,F2<>E2,E2<>B2)),"Gold", IF(E2&F2="00","Silver", IF(AND(E2=D2-1,F2=E2-1),"Bronze", "Ordinary" ))))
Click to expand...
this is my formula for a 4-digit number, would you plz tell me what is the bug that it doesn't work for successive digits like 8765?
Click to expand...
IF(OR(AND(MID(A2, 2, 1)=MID(A2, 3, 1), MID(A2, 3, 1)=MID(A2, 4, 1)), AND(MID(A2, 1, 1)=MID(A2, 3, 1), MID(A2, 3, 1)=MID(A2, 4, 1)), AND(MID(A2, 1, 1)=MID(A2, 2, 1), MID(A2, 2, 1)=MID(A2, 4, 1)), AND(MID(A2, 1, 1)=MID(A2, 2, 1), MID(A2, 2, 1)=MID(A2, 3, 1)), AND(MID(A2, 1, 1)=MID(A2, 2, 1), MID(A2, 3, 1)=MID(A2, 4, 1)), AND(MID(A2, 1, 1)=MID(A2, 3, 1), MID(A2, 2, 1)=MID(A2, 4, 1)), AND(MID(A2, 1, 1)=MID(A2, 4, 1), MID(A2, 2, 1)=MID(A2, 3, 1)), AND(MID(A2, 2, 1)=MID(A2, 1, 1)+1, MID(A2, 3, 1)=MID(A2, 2, 1)+1, MID(A2, 4, 1)=MID(A2, 3, 1)+1), AND(MID(A2, 2, 1)=MID(A2, 1, 1)-1, MID(A2, 3, 1)=MID(A2, 2, 1)-1, MID(A2, 4, 1)=MID(A2, 3, 1)-1)), "Diamond", "none")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Telephone numbers are Hard!
2
Replies
10
Views
340
dowtym
D
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
1K
Sektor
Sektor
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
520
Fluff
Fluff
B
  • Question Question
Stop Excel rounding decimal numbers up to next integer
Replies
5
Views
685
rlv01
R
C
  • Question Question
Extract numbers from a column with the same repeated digits and at the same position as the reference number
Replies
6
Views
794
felixstraube
F

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

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