CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Alex Vega Romero
Alex Vega Romero

Reputation: 1

Google Sheet - Cell with tow formulas

Good afternoon, I want to raise a problem that I have been having for a few weeks and I can not solve. In the first column you can see that I put a FIX message that I receive by e-mail, and what it does is to separate each tag in a column according to its meaning. Tags are always defined by a number, for example |1=RETAIL|.

All this process has been performed with the help of regular expressions: =REGEXEXEXTRACT(A2;"|1=(.+?)|"). The above expression separates each tag and also removes the | symbol.

Now, the next progress I want to achieve is that each cell also checks that the tag is within a range of possible values. For example, tag 21 could be 1,2,3 or A but not 4. First I tried to do it through conditionals like IF, but since it is a REGEXTRACT for each cell, it never checks that the value inside the cell is the corresponding number, but it checks that it is REGEXTRACT. That is, for the previous example, the IF would only return TRUE if the condition is REGEXEXEXTRACT(A2;"|21=(.+?)", but not when it is 1 or 2 or 3...

I have tried to design a custom formula with Apps Script, but I am very new to all this and I am very lost.

Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1N9V9l62kDlaaejxxh6bngBXXiHWM0OcQ7kJO3a_5S8A/edit?usp=sharing

Thx you in advance. Ver pregunta

Upvotes: 0

Views: 40

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15328

Try

=REGEXEXTRACT(A2;"\|[123A^4]=(.+?)\|")

means 1 or 2 or 3 or A but not 4 only one character

or

=query(sort(arrayformula(to_text(split(transpose(split(A2;"|"));"="))));"select Col2 where Col1 matches '1|2|3' and not Col1 matches 'A|B' ")

where you can list where Col1 has to match and not to match

if there is more than one answer, apply textjoin

Upvotes: 0

Related Questions