Using filter & index in Google Sheets formula and maintaining hyperlink

0
votes

I have 1 Google Spreadsheet has master data (no access to users).

Another Google Spreadsheet that uses =IMPORTRANGE("URL of Google Sheet above", "Range")

Within that spreadsheet the above mentioned sheet is hidden, there's another sheet that uses an index formula with filter to only display when data in a column is true (e.g. Status = "Final")

={index('All - By ID'!A2:L2);filter('All - By ID'!A2:L, regexmatch('All - By ID'!H2:H, "Final"))}

That all works as intended.

I have another series of sheets in that same spreadsheet where I want to filter by another column (in addition to the above filter) but could not figure out the regexmatch to get it to work. It's in column D and a numerical value (e.g. all rows with column D value of 5.0, then another sheet for all with value of 5.1).

However, when I put "5.1" in the regexmatch, it also returns values such as 5.11, 5.12, etc, where we only want "5.1".

I tried a query but it does not carry over embedded hyperlinks in cells, and one of the columns is required to have that.

1
regexgoogle-sheets

1 Answers

0
votes

Short answer

Try "^5\.1$" instead of "5.1"

Explanation

Regular expressions assign special meaning to some characters. One of them is . which means "any character". by the other hand if you do not specify the beginning or end of the string, it will look for the first match on the string, and if it found it, will return TRUE.

^ means the beginning $ means the end

But bear in mind that the above meanings could change under certain circumstances. Cheet the info/learn more section of to learn the basics of regular expressions and to find useful resources.