How can I extract the exact part of the text on the cell of google sheet when the text can change?

Question

In a Google Sheets spreadsheet, I have the cell A1 with value "people 12-14 ABC". I want to extract the exact match "ABC" into another cell. The contents of cell A1 can change, e.g. to "woman 60+ ABCD". For this input, I would want to extract "ABCD". If A1 was instead "woman 12-20 CAE", I would want "CAE".

There are 5 possible strings that the last part may be: (ABC, ABCD, AB, CAE, C), while the first portions are very numerous (~400 possibilities).

How can I determine which of the 5 strings is in A1?

Answer 1

If the first part "only" has lower case or numbers and the last part "only" UPPER case,

=REGEXREPLACE(D3;"[^A-E]";)

Anchor: Space

=REGEXEXTRACT(A31;"\s([A-E]+)$")

Answer 2

If you can guarantee well-formatted input, this is simply a matter of splitting the contents of A1 into its component parts (e.g. "gender_filter", "age range", and "my 5 categories"), and selecting the appropriate index of the resultant array of strings.

To convert a cell's contents into an array of that content, the SPLIT() function can be used.

B1 = SPLIT(A1, " ")

would put entries into B1, C1, and D1, where D1 has the value you want - provided your gender filter and age ranges.

Since you probably don't want to have those excess junk values, you want to contain the result of split entirely in B1. To do this, we need to pass the array generated by SPLIT to a function that can take a range or array input. As a bonus, we want to sub-select a part of this range (specifically, the last one). For this, we can use the INDEX() function

B1 = INDEX(SPLIT(A1, " "), 1, COUNTA(SPLIT(A1, " ")))

This tells the INDEX function to access the first row and the last column of the range produced by SPLIT, which for the inputs you have provided, is "ABC", "ABCD", and "CAE".