Forum Discussion
removal of data based on text format
Help please smarter people of the world. I do weird formulas, however this one has me stumped. Request for data sorting based on text format. All Caps text versus First Letter Capital text.
Example comes from peoples name and how they indicate employees or free login users
Employee data is written as 'Doe, Jane'
Free Login is written as 'DOE, JANE'
Sort function to highlight the employee data.
- You're very welcome. Don't forget to mark the answer as "accepted" 😉 Cheers!
3 Replies
Djrtmum Use the EXACT function with the UPPER function to determine which names represent "Free Users". The generic syntax for this would be:
=EXACT(name, UPPER(name))This will return TRUE if the name was originally upper case; otherwise, it will return FALSE.
This method will also accept a range of names and spill the results in an array. For example:
=EXACT(A2:A10, UPPER(A2:A10))To return a list of "Employees" only, place the above formula inside the NOT function, which can be used as the include parameter of the FILTER function:
=FILTER(A2:A10, NOT(EXACT(A2:A10, UPPER(A2:A10))))Or, if sorting is preferred, try something along these lines:
=LET( arr, SORT(A2:A10), FreeUser, LAMBDA(name, EXACT(name, UPPER(name))), SORTBY(arr, FreeUser(arr), -1) )- Absolute champion - Thank-you
- You're very welcome. Don't forget to mark the answer as "accepted" 😉 Cheers!
