google sheets regex multiply values in cell by integer in in same cell-CodePudding

I struggled to find something to answer this, perhaps I'm simply looking for the wrong question:

I have a field that people may put a string into, such as:

colA

G7
2xC55 1xG7
G7
C55

I'm attempting to compare the number of instances by a value (listing the unique values in one column!). Ultimately I want it to read:

colB   colC

G7     3
C55    3

however, using =UNIQUE(FILTER(A1:A4,A1:A2)) Column B will write:

col B

G7
2xC55 1xG7
C55

And if I try to count the number of instances of a value using =COUNTIF(A1:A4,"*"&B1&"*") it will read, in whole:

col A        col B       col C

G7           G7          3
2xC55 1xG7   2xC55 1xG7  1
G7           C55         2
C55

So again, how would I make columns B and C read:

colB   colC

G7     3
C55    3

I have other issues, but once I figure this out, I should be able to apply it forward (as it seems to be a confusing regex extraction listing all in one)

CodePudding user response：

try:

=ARRAYFORMULA(QUERY(IFNA(FLATTEN(SPLIT(FLATTEN(REPT(REGEXEXTRACT(SPLIT(A1:A4, " "), 
 "(?:\d x)?(. )")&"×", IFNA(REGEXEXTRACT(SPLIT(A1:A4, " "), "(\d )x"), 1)*1)), "×"))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''"))