In my Google spreadsheet I have data which updates automatically. I need to see only cells which start with 1. I have used filter but it didn't workout as after each update filter doesn't update so I am getting wrong values (not only those which start with 1). I know that Filter formula can help me but I don't know how to specify "starts with 1" in it. Please let me know if you have any ideas.
0
Add a comment
|
1 Answer
In order to capture only cells starting with 1, you will need to use the REGEXMATCH formula.
(Let's assume you wanted to filter columns A and B, based on whether or not the data in A starts with 1.)
=FILTER(A:B,REGEXMATCH(A:A,"^1"))
The "^1" is a regular expression that captures strings that contain 1 as their first characters.
However, this only works on strings of text, so if your data is numerical, you will need to use the TEXT function in the range argument of your REGEXMATCH to convert the data into text.
=FILTER(A:B,REGEXMATCH(TEXT(A:A,0),"^1"))
