Search code examples
google-sheetsgoogle-sheets-formula

How best to selectively copy cells in Google Sheets If(match... not working

I've got a data set in Google Sheets and I'd like to copy from each cell in F7:H200 range to A7:C200 if the cell in F7:H200 starts with a *.

I was going to put a formula in A7 and copy down and across but I've tried =if(match("*",J8),"J8","")figuring I can probably work out how to make match find anything that starts with a * but tried this first. The problem is that if match fails it just returns N/A in the cell instead of leaving it blank.

Any pointers on how I might do this? Am I heading in the wrong direction trying to use if(match())?

Thanks

Solution

  • You could try this instead:

    =if(regexmatch(J8,"\*"),J8,"")

    This uses REGEXMATCH to match an " * " on the given string value on a cell

    NOTE: This is not yet the most optimal way to approach your problem, but let me know if this works for you.

    enter image description here

    Reference:

    https://learn.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference https://support.google.com/docs/answer/3098292?hl=en