Extract a text between two strings multiple times in Google Spreadsheet

user_16410025 0
Viewed 621

I am using a google spreadsheet to extract specific values from a cell: X: product 1;Y: product 2;Z: product 3

Expected output: X; Y; Z

I managed to extract only the first value: =iferror(regexextract(A1,".+?:.+?")

How can I run it several times?

google-sheets regex
3 Answers

We might be able to use a regex replace approach here:

=REGEXREPLACE(A1, "([A-Z]+):.*?(;|$)", "$1$2")

Demo

Here we are matching and capturing the leading product letter(s) right before the colon. We also match up to, and including, the nearest semicolon separator or the end of the cell. We capture this optional semicolon, and then replace with just $1$2, which are the first two capture groups.

user_18632290

Why use regular expression if you need only the first character of each item:

=ArrayFormula(join(";",left(split(A1,";"),1)))
user_7240390

use:

=INDEX(JOIN(";", REGEXEXTRACT(SPLIT(A1, ";"), "(.+):")))

enter image description here

user_56326290
Related