Returns the Levenshtein edit distance between two text values
=LET(
a, TO_TEXT(text_a),
b, TO_TEXT(text_b),
m, LEN(a),
n, LEN(b),
IF(
m=0,
n,
IF(
n=0,
m,
LET(
init_row, SEQUENCE(n+1, 1, 0, 1),
last_row,
REDUCE(
init_row,
SEQUENCE(m),
LAMBDA(prev_row, i,
LET(
ai, MID(a, i, 1),
REDUCE(
VSTACK(i),
SEQUENCE(n),
LAMBDA(acc, j,
LET(
left, INDEX(acc, ROWS(acc)),
up, INDEX(prev_row, j+1),
diag, INDEX(prev_row, j),
cost, --(ai<>MID(b, j, 1)),
val, MIN(left+1, up+1, diag+cost),
VSTACK(acc, val)
)
)
)
)
)
),
INDEX(last_row, n+1)
)
)
)
)
This function calculates the Levenshtein distance between two strings, which is the minimum number of single-character edits needed to turn one string into the other (insertions, deletions, or substitutions). It's useful for fuzzy matching tasks like deduping lists, catching typos, comparing names, or scoring how similar two inputs are. For example, if A2 contains "kitten" and B2 contains "sitting", using =LEVENSHTEIN_DISTANCE(A2, B2) returns 3 because "kitten" can be changed to "sitting" with three edits.
Use the inputs below to create LEVENSHTEIN_DISTANCE as a reusable custom function in Google Sheets.
Learn how to add custom functions to Google SheetsLEVENSHTEIN_DISTANCE
Returns the Levenshtein edit distance between two text values
=LET( a, TO_TEXT(text_a), b, TO_TEXT(text_b), m, LEN(a), n, LEN(b), IF( m=0, n, IF( n=0, m, LET( init_row, SEQUENCE(n+1, 1, 0, 1), last_row, REDUCE( init_row, SEQUENCE(m), LAMBDA(prev_row, i, LET( ai, MID(a, i, 1), REDUCE( VSTACK(i), SEQUENCE(n), LAMBDA(acc, j, LET( left, INDEX(acc, ROWS(acc)), up, INDEX(prev_row, j+1), diag, INDEX(prev_row, j), cost, --(ai<>MID(b, j, 1)), val, MIN(left+1, up+1, diag+cost), VSTACK(acc, val) ) ) ) ) ) ), INDEX(last_row, n+1) ) ) ) )
text_a
text_b
First text value to compare
A2
Second text value to compare
B2
Other functions in the same category: Text Manipulation & Formatting
Return uppercase initials from a name or phrase
Normalize whitespace without altering punctuation or letter casing
Removes diacritics from text (for example é to e, ü to u)
Removes HTML tags and decodes a few common HTML entities
Extracts text between two delimiters
Lowercases text, trims it, removes punctuation, and collapses whitespace
Named functions enable the creation of custom, reusable formulas that mimic built-in functions, streamlining calculations and data manipulations. These functions simplify complex formulas, making spreadsheets more readable and less prone to errors. By encapsulating intricate logic within a single function call, they enhance consistency across your data. Utilizing named functions reduces the need to write lengthy formulas repeatedly, thereby improving workflow efficiency and productivity in data analysis and management.
Download and import — fastest way to add the function but does not include argument descriptions and examples
Follow these simple steps to download and import a function into your spreadsheet. This method is the quickest and easiest way to add the named function to your Google Sheets document, but will not include the argument description and examples.
Once added, the function will be ready to use in your document like any other built-in function. Simply type the function name and provide the required inputs to use it in your calculations.
Copy and paste — copy and paste each property one at a time
Follow these simple steps to integrate a custom named function into your spreadsheet:
Once added, the function will be ready to use in your document like any other built-in function. Simply type the function name and provide the required inputs to use it in your calculations.