Today we are going to discuss about 3 different methods in Excel to check whether the characters in text are in alphabetical order or not. Lot to learn, let's dive in.
Method-1:
Formula in C4 cell is,
=AND(GESTEP(MMULT(CODE(MID(B4,ROW(INDIRECT("1:"&LEN(B4)-1))+{0,1},1)),{-1;1})))
Don't panic, I will guide you through from scratch,
The concept here is, to extract a pair of adjacent strings and compare them to find the order.
If 3 characters are there in a text, we will compare {1,2} and {2,3}.
Number of pairs are one less than the number of characters in text.
So, the core part is,
=ROW(INDIRECT("1:"&LEN(B4)-1))
If we evaluate above construction, it will look like {1;2}.
For 3 characters we have 2 rows, that's fine but how to create pairs? Simply add 0 and 1.
=ROW(INDIRECT("1:"&LEN(B4)-1))+{0,1}
Output will look like this,
{1,2;2,3}
Use this in MID function to extract each character as pair,
=MID(B4,ROW(INDIRECT("1:"&LEN(B4)-1))+{0,1},1)
{"A","B";"B","C"}
Extract the codes of each character,
=CODE(MID(B4,ROW(INDIRECT("1:"&LEN(B4)-1))+{0,1},1))
{65,66;66,67}
In the first pair, 2nd number is larger than first one.
In the 2nd pair, 2nd number is larger than first one.
So, this is in alphabetical ascending order. To do this operation, we can get help from MMULT function,
=MMULT(CODE(MID(B4,ROW(INDIRECT("1:"&LEN(B4)-1))+{0,1},1)),{-1;1})
we multiply 65 with -1 and 66 with 1, {-65,66}. if we sum these two numbers, result is 1.
Do this for each pair, the result will look like below,
{1;1}
Wrap this in GESTEP function,
=GESTEP(number,[step])
If the number is greater than or equal to step (threshold), the result is 1 else 0.
By default, step is zero.
Wrap this in AND function,
if either of the GESTEP output is zero, then AND function will throw FALSE.
If we take "OORRPQ"
Output of MMULT is {0;3;0;-2;1},
If we wrap it in GESTEP, then the result is {1;1;1;0;1}. Here AND function will deliver FALSE.
Method-2:
Formula in D4 cell is,
=CONCAT(SORT(MID(B4,SEQUENCE(LEN(B4)),1)))=B4
Simple, straight forward and powerful dynamic Array functions.
Step-1: Create the sequence of numbers based on the length of text,
Step-2: Extract each one character from text using MID function,
Step-3: Sort the list of characters and the concatenate,
Step-4: Compare this text with the text in B4 cell, that's it.
Method-3:
Formula in E4 cell is,
=BYROW(B4:B8,LAMBDA(a,CONCAT(SORT(MID(a,SEQUENCE(LEN(a)),1)))=a))
This may look longer, but it is a single cell formula.
This will spill the result from E4:E8.
Lambda function helps us to invoke custom function in each row.
Here, 'a' is the variable which represents current row in B4:B8 range. We can use any name, but the basic naming conventions are applicable.
For ex., in E7 cell, 'a' represents B7 cell value. This will be used in given function.
The function which we used here is same as method 2.
ความคิดเห็น