3
votes
Insert space after every character of a text string
I solved my problem with the following formula:
=TRIM(
REGEXREPLACE(
A1, "(.)", "$1 "))
Credit to @Erik Tyler for suggesting the TRIM function which provided a much ...
2
votes
Accepted
Formula that looks for any matching substrings between two cells
One way to do that is to create a regular expression, like this:
=regexmatch(A1, substitute("\b(" & B1 & ")\b", " ", "|"))
When B1 is foo bar, the ...
- 12.6k
2
votes
Accepted
I have multiple unequally sized rows that I want to combine into a single matrix. How can this be done?
You could try something as simple as =tocol(code_f3!A1:ZZZ,1) to put all the results (minus blanks) into a single column. ZZZ is the maximum number of columns Google Sheets allows (18278).
1
vote
Looking up two criteria in a table, one vertical, one horizontal and finding a value
Basic Formula
Works for a single date/row.
=INDEX(range,
MATCH(name, nameCol, 0),
MATCH(date, dateRow, 0))
Error Handling
Add the IFNA function to hide errors when no value is found.
=IFNA(...
- 2,963
1
vote
Runner's time generates error when divided by distance run regardless of the number format
Enter 00:09:07.7 as in Google Sheets, duration values should include hours.
You might also have to set the cell format to duration as Google Sheets might show durations as time by default.
- 43.9k
1
vote
How to filter combined search outputs with OR logic
To search each row in REFS!A3:AA for partial and case-insensitive matches of any of the text strings in AA1:AD1, use regexmatch(), like this:
=ifna(
filter(
REFS!A3:AA,
regexmatch(
...
- 12.6k
Only top scored, non community-wiki answers of a minimum length are eligible