Get text between colons in Google Sheets

  1. =ARRAYFORMULA(TRIM(REGEXREPLACE(A2, "_|:|\*", " "))) // Get text between colons

  2. =ARRAYFORMULA(TRIM(REGEXREPLACE(A2, "_|,|\*", " "))) // Get text between commas

  3. =ARRAYFORMULA(TRIM(REGEXREPLACE(A2, "_|;|\*", " "))) // Get text between semicolons

A2 = data cell

Check below for a detailed explanation with pictures and how to use formulas in Google Sheets.

Get text between colons in Google Sheets

How to get text between colons in Google Sheets?
Get text between colons in Google Sheets

GET TEXT BETWEEN COLONS — GOOGLE SHEETS FORMULA AND EXAMPLE

  1. =ARRAYFORMULA(TRIM(REGEXREPLACE(A2, "_|:|\*", " ")))

    • Get text between colons

  2. =ARRAYFORMULA(TRIM(REGEXREPLACE(A2, "_|,|\*", " ")))

    • Get text between commas

  3. =ARRAYFORMULA(TRIM(REGEXREPLACE(A2, "_|;|\*", " ")))

    • Get text between semicolons

💡 In Excel "Find and Replace" will do the same job

Other useful extract formulas in Excel and Google Sheets explainedOthers FormulasDate and time functionsNumber based criteriaText-based criteriaExtract functions