CODEWITHSUNDEEP
excel
Kevin Jones
Kevin Jones

Reputation: 429

Extracting first two words from excel before colon

I want to extract the first two words in the following cell C2 before the colon

John Smith: Not attending today

=TRIM(LEFT(C2, FIND("~",SUBSTITUTE(C2, " ", "~ ",2)&"~")))

I tried the above formula but it gives me "John Smith:" it gives me the colon as well

How can I just get John Smith

Upvotes: 0

Views: 2854

Answers (2)

user4039065
user4039065

Reputation:

Alternate,

=REPLACE(C2, FIND(":", C2), LEN(C2), "")
' or the reverse as,
=REPLACE(C2, 1, FIND(":", C2)+1, "")

Upvotes: 1

MarkR
MarkR

Reputation: 302

How about:

=LEFT(C2,FIND(":",C2)-1)

As you probably know, the FIND will tell you what position the colon is at. From there, it seems like a good place to use the "LEFT" function to count X number of characters before that position (the -1 is what makes it stop at the character before the colon)

Note that this only looks for the first colon, and also that if there's no colon, you'll get a "#VALUE" error, so if that's a possibility you're concerned about, you would need to handle it.

Upvotes: 3

Related Questions