CODEWITHSUNDEEP
excelfunctionexcel-formula
thyraxmyst
thyraxmyst

Reputation: 5

How do you extract the first sentence in Excel

I have around 2000 descriptions that need a short description. Here is an example of a description.

Chloe New, the heir of the Original Chloe is warm, feminine and a great signature scent. Chloe is a flowery scent with peonies, freesia, magnolia, lilies and rose petals along with ripe litchis. A hint of woods at the base makes Chloe New the best everyday fragrance. It's long lasting power keeps you fresh all day long.

The result being this

Chloe New, the heir of the Original Chloe is warm, feminine and a great signature scent.

Sometimes other descriptions will end like this, for example:

Chloe New, the heir of the Original Chloe is warm, feminine and a great signature scent. Chloe is

The current function I am using is the "=left(a1,70)" which takes the first 70 characters starting from the left. However, this function doesn't always extract the first sentence but ends at the beginning of the second sentence.



So my question is:

Is there a function that only extracts the first sentence of a cell?

Upvotes: 0

Views: 4901

Answers (3)

Gary's Student
Gary's Student

Reputation: 96763

Add a "space" right after "." to prevent the sentence from being prematurely trimmed in the case of an abbreviation like "e.g.":

=LEFT(A1,FIND(". ",A1))

Upvotes: 2

Mike Powell
Mike Powell

Reputation: 260

if you want to tag off the comma use this:

=LEFT(A1, (SEARCH(",",A1,1))-1)

Upvotes: 0

chishaku
chishaku

Reputation: 4643

Where A1 is the cell you are evaluating:

=LEFT(A1,SEARCH(".",A1))

SEARCH() finds the index of "." and you evaluate your LEFT() function up to that point.

EDIT: For this use case, my solution and Gary's solution are nearly identical. For other use cases, SEARCH might be preferable to FIND because it performs case-insensitive search and also supports wildcard characters.

Upvotes: 0

Related Questions