How to Filter entire column containing a specific text and then remove that specific text from the f-CodePudding

I have Column A and Column B with data.

| Column A | Column B |
| -------- | -------- |
| AlexPaul.first    | 152      |
| LousieB.second   | 320      |
| ClaryA.thrid   | 777      |
| DukeGat.last   | 555      |
| HarryBot.fourth   | 458      |
| JackMalt.thrid   | 852      |
| DannyLouse.last   | 350      |
| UbarKim.thrid   | 788      |
| KyatiGott.thrid   | 796      |
| ElsiyaJaous.last   | 555      |
| GarryBewsh.fivth   | 485      |

I want to filter Column A containing ".last" text in it, and then on the result, I would like to remove the".last" text from the result.

This is how the result should be:

| Column A | Column B |
| -------- | -------- |
| DukeGat   | 555      |
| DannyLouse   | 350      |
| ElsiyaJaous   | 555      |

How I do this with a single formula in Google sheets? As I have this data running 1000 of cells, I was trying ArrayFormula to combine the Filter Function and Substitute function. But I cant get it to work.

In Column D, I added

=FILTER(A:B,SEARCH(".last",A:A))

Then, in Column F, to remove ".last" from the Filtered result, I have used the below formula.

=ARRAYFORMULA(SUBSTITUTE(D2:D,".last",""))

This is two-step process. Is there a better way to do this?

Can we combine this Filter function and Substitute function under a single ArrayFormula? Or is there a better way to do this?

Please help.

CodePudding user response：

You can combine your formulas as

=ARRAYFORMULA(SUBSTITUTE(FILTER(A:B,SEARCH(".last",A:A)),".last",""))

CodePudding user response：

The way I would do this is by using the QUERY function wrapped in a REGEXREPLACE like

ArrayFormula(REGEXREPLACE(QUERY(A1:B20, "SELECT * WHERE A LIKE '%.last'"), ".last", ""))

Where your data is in A1:B20. Since your example has numbers, which will cause REGEXREPLACE to error, we need to overwrite the errors with an IFERROR and the same QUERY formula without the REGEXREPLACE.

IFERROR(
ArrayFormula(REGEXREPLACE(QUERY(A1:B20, "SELECT * WHERE A LIKE '%.last'"), ".last", "")),
QUERY(A1:B20, "SELECT * WHERE A LIKE '%.last'")
)