Extract Words from Cells

[Celticshadow]

Hi All

I seem to have run into a few problems in the last few days and hopefully this will be my last request for a while. What I would like to do is extract the words from one cell and place them in another as shown below.

Thanks in advance.

Kind Regards

[RANGE=cls:xl2bb-100][XR][XH=cs:3]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][/XR][XR][XH]1[/XH][XD=h:c]Data[/XD][XD=h:c]Result Required[/XD][/XR][XR][XH]2[/XH][XD=h:c]Benedict Spirit 12p[/XD][XD=h:c]Benedict Spirit[/XD][/XR][XR][XH]3[/XH][XD=h:c]Cloneen Storm 43P t[/XD][XD=h:c]Cloneen Storm[/XD][/XR][XR][XH]4[/XH][XD=h:c]Commandment 14[/XD][XD=h:c]Commandment[/XD][/XR][XR][XH]5[/XH][XD=h:c]Coursey Rover 42P t[/XD][XD=h:c]Coursey Rover[/XD][/XR][XR][XH]6[/XH][XD=h:c]Dangan Bridge 42P[/XD][XD=h:c]Dangan Bridge[/XD][/XR][XR][XH]7[/XH][XD=h:c]Hazeymm 14[/XD][XD=h:c]Hazeymm[/XD][/XR][XR][XH]8[/XH][XD=h:c]Hes A Magpie 16 p[/XD][XD=h:c]Hes A Magpie[/XD][/XR][XR][XH]9[/XH][XD=h:c]Knockmoy Jewel 42P[/XD][XD=h:c]Knockmoy Jewel[/XD][/XR][XR][XH]10[/XH][XD=h:c]Page One Two Nine 81[/XD][XD=h:c]Page One Two Nine[/XD][/XR][XR][XH]11[/XH][XD=h:c]Paradis De Thaix 93[/XD][XD=h:c]Paradis De Thaix[/XD][/XR][XR][XH]12[/XH][XD=h:c]River DOr 85[/XD][XD=h:c]River DOr[/XD][/XR][XR][XH]13[/XH][XD=h:c]Rock County 71[/XD][XD=h:c]Rock County[/XD][/XR][XR][XH]14[/XH][XD=h:c]Rokococo Palm 37[/XD][XD=h:c]Rokococo Palm[/XD][/XR][XR][XH]15[/XH][XD=h:c]Stylos Ace 16 b[/XD][XD=h:c]Stylos Ace[/XD][/XR][XR][XH]16[/XH][XD=h:c]Vital Plot 16 t[/XD][XD=h:c]Vital Plot[/XD][/XR][XR][XH]17[/XH][XD=h:l]I need to extract the word or words before the numbers and place them in column B.[/XD][XD][/XD][/XR][XR][XH]18[/XH][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD][/XD][XD][/XD][/XR][XR][XH=cs:3][RANGE][XR][XD]...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[bosco_yip]

B2, copy down

=MID(A2,1,MATCH(,INDEX(0*MID(A2,ROW($1:$99),1),),)-2)

Regards

 

[markmzz]

Or try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Data</td><td style="font-weight: bold;;">Result Required</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Benedict Spirit 12p</td><td style=";">Benedict Spirit </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Cloneen Storm 43P t</td><td style=";">Cloneen Storm </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Commandment 14</td><td style=";">Commandment </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Coursey Rover 42P t</td><td style=";">Coursey Rover </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Dangan Bridge 42P</td><td style=";">Dangan Bridge </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Hazeymm 14</td><td style=";">Hazeymm </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">He´s A Magpie 16 p</td><td style=";">He´s A Magpie </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Knockmoy Jewel 42P</td><td style=";">Knockmoy Jewel </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Page One Two Nine 81</td><td style=";">Page One Two Nine </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Paradis De Thaix 93</td><td style=";">Paradis De Thaix </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">River D´Or 85</td><td style=";">River D´Or </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Rock County 71</td><td style=";">Rock County </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Rokococo Palm 37</td><td style=";">Rokococo Palm </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Stylos Ace 16 b</td><td style=";">Stylos Ace </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Vital Plot 16 t</td><td style=";">Vital Plot </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=MID(<font color="Blue">A2,1,MIN(<font color="Red">FIND(<font color="Green">{0;1;2;3;4;5;6;7;8;9},A2&"0123456789"</font>)-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Markmzz

 

[Marcelo Branco]

B2, copy down

=MID(A2,1,MATCH(,INDEX(0*MID(A2,ROW($1:$99),1),),)-2)

Regards

Cool!

M.

 

[T. Valko]

Hi All

I seem to have run into a few problems in the last few days and hopefully this will be my last request for a while. What I would like to do is extract the words from one cell and place them in another as shown below.

Thanks in advance.

Kind Regards

[RANGE=cls:xl2bb-100][XR][XH=cs:3]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][/XR][XR][XH]1[/XH][XD=h:c]Data[/XD][XD=h:c]Result Required[/XD][/XR][XR][XH]2[/XH][XD=h:c]Benedict Spirit 12p[/XD][XD=h:c]Benedict Spirit[/XD][/XR][XR][XH]3[/XH][XD=h:c]Cloneen Storm 43P t[/XD][XD=h:c]Cloneen Storm[/XD][/XR][XR][XH]4[/XH][XD=h:c]Commandment 14[/XD][XD=h:c]Commandment[/XD][/XR][XR][XH]5[/XH][XD=h:c]Coursey Rover 42P t[/XD][XD=h:c]Coursey Rover[/XD][/XR][XR][XH]6[/XH][XD=h:c]Dangan Bridge 42P[/XD][XD=h:c]Dangan Bridge[/XD][/XR][XR][XH]7[/XH][XD=h:c]Hazeymm 14[/XD][XD=h:c]Hazeymm[/XD][/XR][XR][XH]8[/XH][XD=h:c]Hes A Magpie 16 p[/XD][XD=h:c]Hes A Magpie[/XD][/XR][XR][XH]9[/XH][XD=h:c]Knockmoy Jewel 42P[/XD][XD=h:c]Knockmoy Jewel[/XD][/XR][XR][XH]10[/XH][XD=h:c]Page One Two Nine 81[/XD][XD=h:c]Page One Two Nine[/XD][/XR][XR][XH]11[/XH][XD=h:c]Paradis De Thaix 93[/XD][XD=h:c]Paradis De Thaix[/XD][/XR][XR][XH]12[/XH][XD=h:c]River DOr 85[/XD][XD=h:c]River DOr[/XD][/XR][XR][XH]13[/XH][XD=h:c]Rock County 71[/XD][XD=h:c]Rock County[/XD][/XR][XR][XH]14[/XH][XD=h:c]Rokococo Palm 37[/XD][XD=h:c]Rokococo Palm[/XD][/XR][XR][XH]15[/XH][XD=h:c]Stylos Ace 16 b[/XD][XD=h:c]Stylos Ace[/XD][/XR][XR][XH]16[/XH][XD=h:c]Vital Plot 16 t[/XD][XD=h:c]Vital Plot[/XD][/XR][XR][XH]17[/XH][XD=h:l]I need to extract the word or words before the numbers and place them in column B.[/XD][XD][/XD][/XR][XR][XH]18[/XH][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD][/XD][XD][/XD][/XR][XR][XH=cs:3][RANGE][XR][XD]...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Try this...

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-2)

Assumes there will always be some number in each cell.

 

[Rick Rothstein]

Try this...

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-2)

Assumes there will always be some number in each cell.

If you do it like this, then it will not matter if there is a number (or even any text for that matter) in the cell or not...

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

 

[T. Valko]

If you do it like this, then it will not matter if there is a number (or even any text for that matter) in the cell or not...

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

My suggestion is based on the sample data that was posted.

 

[Rick Rothstein]

My suggestion is based on the sample data that was posted.

I understand that... I was just responding to your "Assumes there will always be some number in each cell" statement. In addition, the modification I offered will work even if the formula is copied down the column where no text exists in Column A in anticipation of future data entries (your formula, as written, will generate an error).

 

[T. Valko]

I understand that... I was just responding to your "Assumes there will always be some number in each cell" statement. In addition, the modification I offered will work even if the formula is copied down the column where no text exists in Column A in anticipation of future data entries (your formula, as written, will generate an error).

OK, Rick, I'll play!

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

Then why use that long formula with the extra function call?

This shorter formula will do the same thing:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789"))-2)

 

[Rick Rothstein]

This shorter formula will do the same thing:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789"))-2)

I know the OP's example data did not show this condition, but your formula will return the wrong result if there is no space before the number... I meant to mention that in my original message. Actually, I remember typing that originally, but then I changed the wording of my message before sending it and it appears I forgot to re-include that part... sorry for the confusion that caused.