google-sheets

Google Formula: how to get immediately the first item of a split? Get first result when a formula returns multiple cells


The formula SPLIT(CELL,DELIMITER) fills multiple cells, but i want only for example the first result. How to do this?
In general: when a Google formula returns multiple answers (normally in a represented in a range), how do you only return one value in that range?

UPDATE: I found out the specific case aboven is also possible to solve it with regular expressions for everyone interested:

I found another solution by using regular expression. In this case I want everything up to the ( symbol: =REGEXEXTRACT(CELL;"(.+?) \(") it matches the part between (.+?) and the . means everything, the + means at least one character and the ? means nongreedy, so it will stop when encountering the rest of the expression (which is the ( symbol). Also to avoid the #N/A error when there is no ( symbol, you write: IFNA(REGEXEXTRACT(C116;"(.+?) \(");C116)


Solution

  • I'm not sure I fully understand your goal. But suppose you had the following string in cell A1:

    A,B,C,D,E

    You could obtain A with this formula:

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

    If you wanted to have an array formula process similar strings in the entire column A:A, you would use this in, say, B1 (with the rest of B:B empty):

    =ArrayFormula(IF(A:A="",,ARRAY_CONSTRAIN(SPLIT(A:A,","),ROWS(A:A),1)))