CODEWITHSUNDEEP
arraysregexgoogle-sheetsgoogle-sheets-formulaarray-formulas
Nabnub
Nabnub

Reputation: 1055

REGEXTRACT - to match one letter represented by a special character within a string

I'm facing a little problem to do the following:

I have within cells (in Google sheets) some text, and I'm searching to extract what's after Question 1: More precisely, the missing letter represented by the special character ●

The cell might contains one of the following strings:

Question 1: ●BCD

Question 1: A●CD

Question 1: AB●D

Question 1: ABC●

The letters extracted can only be (A,B,C,D) in capital, so in the first example, I should extract the letter A, in the second the letter B, in the third the letter C, and in the last example the letter D.

After searching for a while I was able to write the following:

=IFERROR(trim(upper(regexextract(trim(clean(substitute(B2,char(160)," "))),"Question 1:(\s?[a-dA-D])"))),"??")

But this extract always the letter A, and if the special character is at the beginning (example 1) I get an error.

Another similar scenario is again to extract the letters T or F in the following:

Question 2 : ●F (here we should extract T)

Question 2 : T● (here we should extract F)

Thank you for showing some lights on these issues.

Upvotes: 1

Views: 331

Answers (3)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TRANSPOSE(IFNA(VLOOKUP(TRIM(SUBSTITUTE(QUERY(TRANSPOSE(
 REGEXREPLACE(SPLIT(B2, CHAR(10)), "Question \d+: ", "♦")), 
 "where Col1 contains '♦'"), "♦", )), 
 {"BCD",  "A";
  "A CD", "B";
  "AB D", "C";
  "ABC",  "D";
  "F",    "T";
  "T",    "F"}, 2, 0))))

0

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(CHAR(FINDB("●", A1:A)-FINDB(":", A1:A)+63)))

or:

=ARRAYFORMULA(IFERROR(CHAR(FINDB("●", A1:A)-LEN(REGEXEXTRACT(A1:A, "(.+: )"))+64)))

0

=ARRAYFORMULA(SUBSTITUTE(IFERROR(CHAR(FINDB("●", A1:A)-FINDB(":", A1:A)+82)), "U", "F"))

or:

=ARRAYFORMULA(SUBSTITUTE(IFERROR(CHAR(FINDB("●", A1:A)-
 LEN(REGEXEXTRACT(A1:A, "(.+: )"))+83)), "U", "F"))

0

UPDATE:

I need to transpose the results horizontally, as I'm aiming to extract the letter for each question in a different cell

=ARRAYFORMULA(TRANSPOSE(ARRAY_CONSTRAIN(IFERROR(IF(REGEXMATCH(A2:A, "T●|●F"), 
 SUBSTITUTE(CHAR(FIND("●", A2:A)-FIND(":", A2:A)+82), "U", "F"), 
            CHAR(FIND("●", A2:A)-FIND(":", A2:A)+63))), COUNTA(A2:A), 1)))

0

Upvotes: 0

Soc
Soc

Reputation: 7780

You don't need regex to achieve this. See the following example: https://docs.google.com/spreadsheets/d/1iMt0pUeyenIAzdHAw2f5jhT41B-Tz86Ab3YfZgXhKk0/edit#gid=0

Question 1 formula:

=MID("ABCD", FIND("●", A2) - FIND(":", A2) - 1, 1)

Question 2 formula:

=MID("TF", FIND("●", A6) - FIND(":", A6) - 1, 1)

The linked sheet breaks down procedurally how you can arrive at this by:

  1. Finding the position of the character you are looking for FIND("●", A2) in the original text
  2. Converting that to a relative position <position> - FIND(":", A2) - 1
  3. Returning the character at the specific position MID("ABCD", <relative position>, 1)

Upvotes: 1

Related Questions