google-sheet : extract sub text from text

Question

I have cells and each contains these text :

my_col_10x50_new
my_col_20x40_new
my_col_30x30_new
my_col_15x50_new

I want correspondingly to extract just the number parts and make a "xx and xx" format like these :

10 and 50
20 and 40
30 and 30
15 and 50

Anyone can help ?

Answer 1

You can try a combination of substitute and regex extract:

=SUBSTITUTE(REGEXEXTRACT(A1, "[0-9]+x[0-9]+"),"x", " and ")

The REGEXEXTRACT extracts the numbers along with the x in the middle and substitute replaces the x with the 'and' in the middle keeping the numbers as is.

Answer 2

use:

=INDEX(IFNA(REGEXREPLACE(REGEXEXTRACT(A1:A, "\d+x\d+"), "x", " and ")))

Answer 3

Another option:

=INDEX(SPLIT(SUBSTITUTE(A1:A,"x"," and "),"_"),0,3)