How to get First Text from cell?

[Mathman]

How can I get the first word from these examples?​

​

Sprint MINI CATEGORIES

Sprint_to_Fastest MINI CATEGORIES

Sprint_to_Lowest MINI CATEGORIES

Sprint_to_Day_Morning MINI CATEGORIES

Sprint_to_Late_Night_Weekly MINI CATEGORIES

<colgroup><col width="314" style="width: 313.33px;"> </colgroup><tbody>
</tbody>

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; border-bottom-color: rgb(34, 34, 34); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(34, 34, 34); border-left-style: none; border-left-width: 0px; border-right-color: rgb(34, 34, 34); border-right-style: none; border-right-width: 0px; border-top-color: rgb(34, 34, 34); border-top-style: none; border-top-width: 0px; font-family: sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: 19.5px; outline-color: transparent; outline-style: none; outline-width: 0px; overflow: visible; width: auto;"></strike>

Assume this example is in cells A1 threw A5
I need to get results in cells B

1 threw B5 and I need them all to = Sprint​

​

Any suggestions?​

​

Thank you​

MM​

​

 

[mumps]

There may be a shorter version but this should work. Place this formula in B1 and copy it down to B5:
=IF(ISNUMBER(SEARCH("_",A1))=TRUE,LEFT(A1,FIND("_",A1,1)-1),LEFT(A1,FIND(" ",A1,1)-1))

 

[Mathman]

that's great! Thank you.

MM

 

[jtakw]

Hi,

This will also work:

Book1
	A	B
1	Sprint MINI CATEGORIES	Sprint
2	Sprint_to_Fastest MINI CATEGORIES	Sprint
3	Sprint_to_Lowest MINI CATEGORIES	Sprint
4	Sprint_to_Day_Morning MINI CATEGORIES	Sprint
5	Sprint_to_Late_Night_Weekly MINI CATEGORIES	Sprint
Sheet100

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"_"," ")," ",REPT(" ",100)),100))

Formula copied down.

 

[jtakw]

Also, formula in Post #2 can be shortened to this:

=IFERROR(LEFT(A1,FIND("_",A1,1)-1),LEFT(A1,FIND(" ",A1,1)-1))

 

[Rick Rothstein]

Also, formula in Post #2 can be shortened to this:

=IFERROR(LEFT(A1,FIND("_",A1,1)-1),LEFT(A1,FIND(" ",A1,1)-1))

You can shorten it even further by doing it this way...

=LEFT(A1,IFERROR(FIND("_",A1),FIND(" ",A1))-1)

 

[jtakw]

Yes, Rick.

 

[Mathman]

awesome, thanks!

 

[jtakw]

You're welcome.