2

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 linebreak between each color so the first example in fact looks like this:

Cell A1= Black char(10) Black

I'd like to keep only one appearence 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 whitin a single cell. How can I acomplish this?

2

This worked on both your examples:

=REGEXEXTRACT(A1,"\w+")
1

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 '15 at 8:23
  • How would your formula deal with Black Black and Black White? – Jacob Jan Tuinstra Jun 24 '15 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 '15 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 '15 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 '15 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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