excel extract text from cell after spaces

[dharvey]

hi all new to the board, I am trying to extract information from one cell into three different cells. My main issue is that not all of the descriptions in the cells are anything alike, see below for an example of seven different part descriptions

Description 1
[TABLE="width: 269"]
<colgroup><col></colgroup><tbody>[TR]
[TD]GUIDE-SHEET EDGE
12.19 HI 1.62 BORE[/TD]
[/TR]
[TR]
[TD]
Description 2
MOUNT-SHAFT
1.50" ADJ

Description 3[/TD]
[/TR]
[TR]
[TD]MOUNT-GUIDE ADJ
7.81 LG 1.58 DIA HOLE[/TD]
[/TR]
[TR]
[TD]
Description 4
MOUNT-GUIDE ROD 1.58 DIA
7.81 W/17.58 LG ANGLED

Description 5[/TD]
[/TR]
[TR]
[TD]MOUNT-GUIDE ADJ
6.63 LG 1.58 DIA HOLE[/TD]
[/TR]
[TR]
[TD]
Description 6
MOUNT-GUIDE ROD 1.58 DIA
7.81 LG W 13.94 ANGLED[/TD]
[/TR]
[TR]
[TD]
Description 7
PLUG-TUBE END
2.38X2.38X.25R
USE W/806X00070-3.00SQ X .25W


[/TD]
[/TR]
</tbody>[/TABLE]
so for the first cell (I call it PartDesLine1) I use

=LEFT(B4,FIND("-",B4))

and it works fine. For the second cell (i call it PartDesLine2) I use

=MID(B4,FIND("-,B4)+1,FIND("*",SUBSTITUTE(B4," ","*",2)))

and it works most of the time except on descriptions 4, 6, and 7

it comes out for description 4 = GUIDE ROD 1.58 D (Which should be DIA instead of D)
it comes out for description 6 = GUIDE ROD 1.58 D (Which should be DIA instead of D)
it comes out for description 7 = TUBE END 2.38 (should be 2.38X2.38X.25R)

and then the third cell (I call it PartDesLine3) I use

=MID(B4,FIND("*",SUBSTITUTE(B4," ","*",3))+1,FIND("*",SUBSTITUTE(B4," ","*",5)))

it comes out for description 4 = DIA 7.81 W/17.58 LG ANGLED (DIA should be in PartDesLine2)
it comes out for description 6 = DIA 7.81 W/13.94 LG ANGLED (DIA should be in PartDesLine2)
it comes out for description 7 = W/806X00070-3.00SQ X .25W


any help would be great thanks

 

[DanteAmor]

Welcome to the forum!

---------------
I lost a bit, you have the 7 descriptions inside cell B4, something like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:274.69px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td></tr><tr style="height:422px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >Description 1<br />GUIDE-SHEET EDGE<br />12.19 HI 1.62 BORE<br />Description 2<br />MOUNT-SHAFT<br />1.50" ADJ<br />Description 3<br />MOUNT-GUIDE ADJ<br />7.81 LG 1.58 DIA HOLE<br />Description 4<br />MOUNT-GUIDE ROD 1.58 DIA<br />7.81 W/17.58 LG ANGLED<br />Description 5<br />MOUNT-GUIDE ADJ<br />6.63 LG 1.58 DIA HOLE<br />Description 6<br />MOUNT-GUIDE ROD 1.58 DIA<br />7.81 LG W 13.94 ANGLED<br />Description 7<br />PLUG-TUBE END<br />2.38X2.38X.25R<br />USE W/806X00070-3.00SQ X .25W</td></tr></table>

---------------

Or you have 7 cells in this way:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:266.14px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >Description 1<br />GUIDE-SHEET EDGE<br />12.19 HI 1.62 BORE</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >Description 2<br />MOUNT-SHAFT<br />1.50" ADJ</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >Description 3<br />MOUNT-GUIDE ADJ<br />7.81 LG 1.58 DIA HOLE</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >Description 4<br />MOUNT-GUIDE ROD 1.58 DIA<br />7.81 W/17.58 LG ANGLED</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >Description 5<br />MOUNT-GUIDE ADJ<br />6.63 LG 1.58 DIA HOLE</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >Description 6<br />MOUNT-GUIDE ROD 1.58 DIA<br />7.81 LG W 13.94 ANGLED</td></tr><tr style="height:74px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >Description 7<br />PLUG-TUBE END<br />2.38X2.38X.25R<br />USE W/806X00070-3.00SQ X .25W</td></tr></table>


----------
And most importantly, what is the result you expect from each of your examples.

 

[dharvey]

sorry if i explained poorly, the second option, they are one description one cell. I'm not sure how to take a snap shot and post it for an example. i am expecting that PartDesLine2 will display everything after the hyphen to the second or third space and PartDesLine3 displays every thing after the second/third space

 

[DanteAmor]

sorry if i explained poorly, the second option, they are one description one cell. I'm not sure how to take a snap shot and post it for an example. i am expecting that PartDesLine2 will display everything after the hyphen to the second or third space and PartDesLine3 displays every thing after the second/third space

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.


you can post small Excel screen shots using HTMLmaker, review this:
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[dharvey]

Thanks for the info see below for my screen shot

 

[dharvey]

sorry still having issues with pictures see drop box link

https://www.dropbox.com/s/npfkrqkv44lvy86/inputoutput.PNG?dl=0

 

[DanteAmor]

sorry still having issues with pictures see drop box link

But you sent the screen with errors, what I want to see are the desired results.
And also if you can explain what the patterns would be.

 

[dharvey]

my apologizes i miss understood, see link

https://www.dropbox.com/s/prvkmmpxvrk00qb/inputoutputdISIRED.PNG?dl=0

 

[dharvey]

my goal is for the PartDesLine2 to satrt at the dash and go to the fourth space and for PartDesLine3 to start at the forth space and end at say 8th space

 

[DanteAmor]

my goal is for the PartDesLine2 to satrt at the dash and go to the fourth space and for PartDesLine3 to start at the forth space and end at say 8th space

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:23.76px;" /><col style="width:428.67px;" /><col style="width:76.04px;" /><col style="width:256.63px;" /><col style="width:226.22px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >GUIDE-SHEET EDGE 12.19 HI 1.62 BORE</td><td >GUIDE-</td><td >SHEET EDGE 12.19 HI</td><td >1.62 BORE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >MOUNT-SHAFT 1.50" ADJ</td><td >MOUNT-</td><td >SHAFT 1.50" ADJ</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >MOUNT-GUIDE ADJ 7.81 LG 1.58 DIA HOLE</td><td >MOUNT-</td><td >GUIDE ADJ 7.81 LG</td><td >1.58 DIA HOLE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >MOUNT-GUIDE ROD 1.58 DIA 7.81 W/17.58 LG ANGLED</td><td >MOUNT-</td><td >GUIDE ROD 1.58 DIA</td><td >7.81 W/17.58 LG ANGLED</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >MOUNT-GUIDE ADJ 6.63 LG 1.58 DIA HOLE</td><td >MOUNT-</td><td >GUIDE ADJ 6.63 LG</td><td >1.58 DIA HOLE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >MOUNT-GUIDE ROD 1.58 DIA 7.81 LG W 13.94 ANGLED</td><td >MOUNT-</td><td >GUIDE ROD 1.58 DIA</td><td >7.81 LG W 13.94 ANGLED</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >PLUG-TUBE END 2.38X2.38X.25R USE W/806X00070-3.00SQ X .25W</td><td >PLUG-</td><td >TUBE END 2.38X2.38X.25R USE</td><td >W/806X00070-3.00SQ X .25W</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C4</td><td >=LEFT(B4,FIND("-",B4))</td></tr><tr><td >D4</td><td >=TRIM(MID(SUBSTITUTE(B4," ",REPT(" ",100)),FIND("-",B4)+1,400))</td></tr><tr><td >E4</td><td >=TRIM(MID(SUBSTITUTE(B4," ",REPT(" ", 100)),400, 500))</td></tr></table></td></tr></table>