SOLVED

Possible to remove text between delimiters that comes from the =FILTER formula?

Copper Contributor

I am using the Azure Devops plugin to retrieve data from a query in Devops in one worksheet of my workbook. In the second worksheet, I am using the =FILTER formula to display text from a range of columns when certain criteria is met.

 

For example I am using the following filter function: 

=FILTER(QUERY!B:B:QUERY!E:E,((QUERY!B:B="Product Backlog Item"))*((QUERY!H:H="POC; POC Signoff")),"No new/ to to tasks")

 

This returns data from columns called work item type, title, assigned to, and status. 

My assigned to column sometimes has a persons name, and sometimes has the person's name followed with an email address in between delimters "<>"

 

Example of data in "Assigned to" column

John Doe

Jane Doe <email address removed for privacy reasons>

Jerry Doe

 

Since the data is coming from Devops, I cannot easily remove the email address. 

 

Is there a method to remove it/hide it from showing in Excel while still keeping the formula intact? I'd like all of the names displayed in the column to not include an email address. 

 

Let me know if I need to provide more detail. 

Thank you. 

 

 

1 Reply
best response confirmed by NSUMike (Copper Contributor)
Solution

@NSUMike 

Here's a scaled down example with TEXTBEFORE:

 

=LET(filtered,FILTER(name,team="red"),TEXTBEFORE(filtered,"<",,,,filtered))

 

Patrick2788_0-1686855244200.png

 

1 best response

Accepted Solutions
best response confirmed by NSUMike (Copper Contributor)
Solution

@NSUMike 

Here's a scaled down example with TEXTBEFORE:

 

=LET(filtered,FILTER(name,team="red"),TEXTBEFORE(filtered,"<",,,,filtered))

 

Patrick2788_0-1686855244200.png

 

View solution in original post