SOLVED

removal of data based on text format

Copper Contributor

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.

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
best response confirmed by Djrtmum (Copper Contributor)
Solution
You're very welcome. Don't forget to mark the answer as "accepted" ;) Cheers!
1 best response

Accepted Solutions
best response confirmed by Djrtmum (Copper Contributor)
Solution
You're very welcome. Don't forget to mark the answer as "accepted" ;) Cheers!

View solution in original post