I've got cells in a column populated with data pulled from another tab using A1= Results_ES!C1 and A2= Results_ES!C2 etc.

Some of the data is duplicated whithin the cell, e.g.

Cell A1= Black Black

Cell A1 = White White

...and so on.
Basically, there's a line-break - char(10) - between each color so the first example (cell A1) in fact looks like this:


I'd like to keep only one appearance of each color, i.e., A1= Black Ideally, I´d like the processing to happen automatically. In other words, I'd like to open the document and find it already corrected for me without having to manually run scripts.

I found some commands that might help such as LEFT, RIGHT, MID, TRIM, LEN, FIND, SEARCH, SUBSTITUTE, REPT, etc., but they are generally used to work with two different cells, not within a single cell. How can I accomplish this?

2 Answers 2


This worked on both your examples:


You can keep only the part until the linebreak with the following REGEXEXTRACT command:

=REGEXEXTRACT(Results_ES!C1, "^.*")

If you also want to remove any spaces in your text before the linebreak, add TRIM:

=TRIM(REGEXEXTRACT(Results_ES!C1, "^.*"))
  • Thnak you!! For the record, this is what I´ve done: =REGEXEXTRACT(Results_ES!S2, "^.*")
    – Pablo
    Jun 24, 2015 at 8:23
  • How would your formula deal with Black Black and Black White? Jun 24, 2015 at 11:15
  • It wouldn't; I addressed only the sort of input actually stated by the OP, without guessing at what else their spreadsheet might contain.
    – user79865
    Jun 24, 2015 at 12:57
  • 1
    @Jacob Jan Tuinstra - in this particular case I only have two pairs of the same word separated by a line break so 1999 answer does the job, but thank you for your observation
    – Pablo
    Jun 29, 2015 at 7:15
  • I've just received a new list of data to process but this time cells have duplicate content separated by a semicolon and a space like so: Black; Black | White; White. How could I modify the formula to obtain only one occurrence of Black and White?
    – Pablo
    Jun 29, 2015 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.