Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

TEXT.SELECT -My Custom Excel Function

Copper Contributor

I would like to talk to you about one of my favorite Excel Functions that I created with the help of LAMBDA

If you always need to separate/extract  the components of a cell so that you can use each part separately, then I think this formula will be useful for you.

TEXT.SELECT
This formula allows the user to select a specific piece of text from a cell (in the same way as the Text.Select in PowerQuery).

It consists of 4 main parts
 
 =TEXT.SELECT ( MixedText ,    Select   , Space   , Keep)

MIXEDTEXT :   Select the Cell

SELECT :
Type  1  for Numbers
Type  2 f or English  Letters
Type  3   for Uppercase English  Letters
Type  4   for Lowercase English  Letters
Type  5   for Other Languages (Some languages are still not supported)

 
SPACE :

 Type 1 to Keep the Space
 Skip this part if you want to ignore the space

Keep :

In this part , if you want to keep Non-alphanumeric type them like this {".";"@";"-"} between Quotations and separated by the semicolon.

 If you want to ignore this part , then type ""

 

Example #1:

in this example I am showing how to extract :Number, All Letters , Uppercase Letters , Lowercase Letters and any other language if found

 

2024-01-29_085844.png

 

Example #2:

How to Extract emails with all its components like  [ @ , . , - , _ ] 

2024-01-29_085957.png

Example #3:

How to Extract emails and any text in different (and as I mentioned Some languages are still not supported) carefully notice that ,It is possible to find a space between sentences, and it is also possible not to find one. So, TEXT.SELECT handles this easily..

 

2024-01-29_090044.png

WHAT DO YOU THINK ? IS IT USEFUL ? WOULD IT SAVE TIME?

#Hazem_Hassan #Dr_Excel #excel #excelskills #exceltricks #exceltips #mvp #mvpdevelopment #mvpbuzz #mvps #MicrosoftAmbassador  

0 Replies