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

 

[dharvey]

thanks so mush that seems to do the trick. I'm not familiar with "REPT" or the reasoning for the 100, 400 and 500 values. can you explain please?

 

[DanteAmor]

thanks so mush that seems to do the trick. I'm not familiar with "REPT" or the reasoning for the 100, 400 and 500 values. can you explain please?

Of course:

GUIDE-SHEET EDGE 12.19 HI 1.62 BORE

=TRIM(MID(SUBSTITUTE(B4," ",REPT(" ",100)),FIND("-",B4)+1,400))

SUBSTITUTE(B4," ",REPT(" ",100)
Susbtitute " " by " " 100 times

Then you have something like this:

MID("GUIDE-SHEET (100) EDGE (100) 12.19 (100) HI (100) 1.62 BORE"),FIND("-",B4)+1,400
<-This data starts after 400 spaces, that's why from the script 400 characters are extracted.

 

[dharvey]

thanks for the info

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.