How to get First Text from cell?

Mathman

Board Regular
Joined
Jan 28, 2017
Messages
144
Office Version
  1. 2016
Platform
  1. Windows


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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,581
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))
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

This will also work:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Sprint MINI CATEGORIES</td><td style=";">Sprint</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sprint_to_Fastest MINI CATEGORIES</td><td style=";">Sprint</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sprint_to_Lowest MINI CATEGORIES</td><td style=";">Sprint</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Sprint_to_Day_Morning MINI CATEGORIES</td><td style=";">Sprint</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Sprint_to_Late_Night_Weekly MINI CATEGORIES</td><td style=";">Sprint</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet100</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=TRIM(<font color="Blue">LEFT(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A1,"_"," "</font>)," ",REPT(<font color="Purple">" ",100</font>)</font>),100</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

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

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

:) Yes, Rick.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,075
Messages
5,526,667
Members
409,714
Latest member
diamondjoechubbs

This Week's Hot Topics

Top