Google Sheets function to check containing string + evaluation

Question

I would really appreciate if someone could help me with this.

I have a file in Google Sheets with one column that could have information in there like this:

piktogramm_ghs02.jpg,piktogramm_ghs07.jpg,
piktogramm_ghs08.jpg,
piktogramm_ghs09.jpg

I want to check if that field contains "02" and if thats the case, I want to fill another column with "02". I got this working like this

=IFS(REGEXMATCH(BC2; "02"); "02"; REGEXMATCH(BC2; "07"); "07"; REGEXMATCH(BC2; "08"); "08")

but it stops checking if it found one matching result.

Is there a way to make a comma-separated list from all pictures like this?

02,07,08,09

Any help would be appreciated :-)

Presumably you couldn't just used a MID formula ?(https://support.google.com/docs/answer/3094129?hl=en-GB) — maxhob17, Mar 04 '20 at 13:42
How many numbers are we talking about? I mean the range number. Is it 00-99? or is just 02,07,08,09? Also, the text `piktogramm_ghs` is always the same? — Foxfire And Burns And Burns, Mar 04 '20 at 14:20

Nirostar · Answer 1 · 2020-03-04T14:26:55.743

You could use:

=IF(REGEXMATCH(A2; "02"); "02,";"")&IF( REGEXMATCH(A2; "07"); "07,";"")&IF(REGEXMATCH(A2; "08"); "08,";"")

if you don't like the last comma, you can use.

=left(IF(REGEXMATCH(A2; "02"); "02,";"")&IF( REGEXMATCH(A2; "07"); "07,")&IF(REGEXMATCH(A2; "08"); "08,";"");len(IF(REGEXMATCH(A2; "02"); "02,";"")&IF( REGEXMATCH(A2; "07"); "07,";"")&IF(REGEXMATCH(A2; "08"); "08,";""))-1)

Or if all your files are called piktogramm_ghs##.jpg then you could use SUBSTITUTE.

=SUBSTITUTE(SUBSTITUTE(A2;"piktogramm_ghs";"");".jpg";"")

This would also be a possibility if the number of different filenames is smaller than the number of different numbers, by just extending the substitutions.

In Google Sheets you can also use Regexreplace.

=REGEXREPLACE(A2;"[^\d,]*";"") would delete anything but digits and commas leading to the same result with your given example.

Voted up for the SUBSTITUTE part. I was thinking the same. And for OP, just a suggestion: consider replacing `,` with `;` because if the value in the column is just 2 pictures (`piktogramm_ghs02.jpg,piktogramm_ghs07.jpg`) the output would be a decimal number (`2,07)` not a string. With `;` you would obtain `02;07` — Foxfire And Burns And Burns, Mar 04 '20 at 14:24

score 1 · Answer 2 · answered Mar 04 '20 at 17:48

1

try:

=ARRAYFORMULA(REGEXREPLACE(SUBSTITUTE(TEXTJOIN(, 1, 
 IFERROR(REGEXEXTRACT(SPLIT(A:A, ","), "[0-9\.]+"))), ".", ","), ",$", ))

answered Mar 04 '20 at 17:48

player0

124,011
12
67
124

Google Sheets function to check containing string + evaluation

2 Answers2