Google-sheets – Finding all word pairs from range of strings in Google Sheets

google sheetsgoogle-sheets-arrayformula

Let's say I have a column of values, each of which is a string.

COL A
This is a string
This is longer than that
My favorite is short
But sometimes short is longer
How is a short longer

I would like to extract all of the two word combinations from this range and then count them.
The unique word pairs would be (ignoring capitalization).

this is – 2
is a – 2
a string – 1
is longer -1
longer than -1
than that -1
my favorite -1
favorite is -1
is short – 1 (etc.)
but sometimes
sometimes short
short is
is longer
how is
a short
short longer

I've tried split/join combinations, but I can't figure out how to sequence the discovery of each pair.

Best Answer

See test sheet.

Unique pairs of 2 word from column A are in column D as a result of following formula:

=ARRAYFORMULA(
 UNIQUE(
  FILTER(
  {"#";TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))}&" "&{TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "));"#"},
  NOT(REGEXMATCH({"#";TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))}&" "&{TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "));"#"},".*#")))))

Idea is make column of single words and concatenate is with the same column offset by 1 row.

Following part returns column of single words.

TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))

Then we shift it down, please note that I used "#" symbol for shifting purposes and further filter and if original text contains "#" it won't be included in final result, so you can change that symbol if you need.

{"#";TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))}

concatenate with other column and filter rows that would contain only one word.

Related Solutions

Google-sheets – Jagged array from Google Spreadsheet range

A custom function will always receive either a rectangular array or a scalar value (number, string, boolean, or datetime) as each of its arguments. So the conversion from rectangular to jagged array has to happen inside the function. For example, by calling jagged(arr) on the input, where jagged is the following function:

function jagged(arr) {
  var rowLength = arr.map(function(row) {
    return row.reduce(function (a, b, i) {
      return b === '' ? a : i+1;
    }, 0);
  });
  return arr.map(function(row, i) {
    return row.slice(0, rowLength[i]);
  });
}

This function first computes the actual length of each row and then truncates the rows accordingly.

I emphasize that the call to jagged needs to happen from within the custom function that will use it, like so:

function myFunction (arr) {
  jarr = jagged(arr); 
  // proceed to work with the jagged array
}

Putting =myFunction(jagged(A1:F3)) in a spreadsheet cell won't do it: the Sheets will automatically inflate the return values of jagged to a rectangular array before passing them to myFunction.

How to Add a Link to Google Search in a Range in Google Sheets

You could use INSERT > Link... to add a link to the active cell but it will require you to write the URL.

If you want that the URL be added automatically, you will need a Google Apps Script.

Instead, you could use a formula with the HYPERLINK function in a temporary column and use some built-in functions to do bulk operations.

I.E. if the search term is in A1 then use the following formula:

=hyperlink("www.google.com/search?q="&A1,A1)

Fill down as necessary.
Copy and paste as values over the column with the search terms.
Delete the temporary column.

Related keyboard shortcuts

Insert a link: CTRL+k
Copy: Ctrl+c
Paste as values: Ctrl+Shift+v

Best Answer

Related Solutions

Google-sheets – Jagged array from Google Spreadsheet range

How to Add a Link to Google Search in a Range in Google Sheets

Related keyboard shortcuts

References

Related Topic