excel extract text from cell after spaces

dharvey

New Member
Joined
Jul 12, 2019
Messages
10
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
GUIDE-SHEET EDGE
12.19 HI 1.62 BORE

Description 2
MOUNT-SHAFT
1.50" ADJ

Description 3
MOUNT-GUIDE ADJ
7.81 LG 1.58 DIA HOLE

Description 4
MOUNT-GUIDE ROD 1.58 DIA
7.81 W/17.58 LG ANGLED

Description 5
MOUNT-GUIDE ADJ
6.63 LG 1.58 DIA HOLE

Description 6
MOUNT-GUIDE ROD 1.58 DIA
7.81 LG W 13.94 ANGLED

Description 7
PLUG-TUBE END
2.38X2.38X.25R
USE W/806X00070-3.00SQ X .25W



<colgroup><col></colgroup><tbody>
</tbody>
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
 
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?
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Last edited:
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top