CODEWITHSUNDEEP
arraysregexgoogle-sheetsnewlinetext-extraction
Justin Benfit
Justin Benfit

Reputation: 483

Extract only first line in each cell in google sheets

I want to extract only the first line of text in each cell. (Sorry for screenshots I can't figure out another way to illustrate). enter image description here

I am using this formula in the Company column to find the new-line character and extract the text to the left of it:

=LEFT(G2,Find(Char(10),G2))

If I manually go through and highlight the entire cell in the Description column then hit enter it will add a newline character after the text in the first line but otherwise there is no newline character present. Hence the #VALUE error. Any ideas how to bulk insert a newline character into every cell so the existing function will work? Open to other solutions as well. Thanks.

Upvotes: 2

Views: 7604

Answers (3)

Alan
Alan

Reputation: 10145

You need to find the the position of char that goes to the next line CHAR(10) with the formula FIND().

Then with Mid(), you select the substring you need to get line1 and line2.

here you have the full example enter image description here

=find(CHAR(10) ; A2)   // Position of the char that goes to the next line
=mid(A2 ; 1 ; C2 -1)   // First Line
=mid(A2 ; C2 ; 999999) // Second Line

Upvotes: 0

Tahlor
Tahlor

Reputation: 1870

IF(REGEXMATCH(A1, Char(10)),LEFT(A1,Find(Char(10),A1)-1),A1)

Pseudo code:

if \newline_char is found in A1:    # REGEXMATCH(A1, Char(10)):
    return first line of cell excluding \newline_char # LEFT(A1,Find(Char(10),A1)-1)
else:
    return A1

Upvotes: 2

player0
player0

Reputation: 1

you can do:

=LEFT(G2&CHAR(10), FIND(CHAR(10), G2&CHAR(10)))

update 1:

delete everything in column A and use this in A1:

={"Company"; INDEX(IFNA(REGEXEXTRACT(B2:B, "(.+)\n"), B2:B))}

enter image description here

update 2:

={"Company"; INDEX(TRIM(IFNA(REGEXEXTRACT(
 REGEXREPLACE(B2:B, "(\d+)", CHAR(10)&"$1"), "(.+)\n"), B2:B)))}

enter image description here

Upvotes: 6

Related Questions