extracting text between variable set variables

[kevinlasvegas]

DESCRIPTION

9600DS 3/16OD CXC DIMP-STOP COUPLING

9600RS 3/16OD CXC ROLL-STOP COUP W01001

9600DS 1/4 CXC DIMP-STOP COUPLING W10141

9600RS 1/4 CXC ROLL-STOP COUPLING W01003

9600R 1/4X3/16OD CXC RED COUPLING W01004

9600DS 5/16OD CXC DIMP-STOP COUP W10142

9600RS 5/16OD CXC ROLL-STOP COUP W01006

9600R 5/16ODX1/4 CXC RED COUPLING

<tbody>
</tbody>

Hello Mr Excel,
Long time lurker, first time poster.
My data set has a variable length ALPHANUMERIC LEADER and in some cases a variable length ALPHANUMERIC TRAILER that always,(when it is there) starts in a "A" or "W" followed by a set of numbers.
The LEADER is always separated by a space AFTER it and the leader is in every cell.
The TRAILER (when present) is always separated by a space BEFORE the "A" or "W" but may not exist in every cell.
This means I can not extract with a MID function which will extract between the first and last space. If no TRAILER exists I need the LEADER stripped and the results returned without an error resulting.
I guess it is a combination of IF, FIND,LEFT, RIGHT all in one nested formula. I have tried every variation of a MID function nested with CRITERIA functions and I have not hit the jackpot yet. Now I have run out of ideas. I need serious help.
Thank you.

 

[István Hirsch]

	B	C
3	9600DS 3/16OD CXC DIMP-STOP COUPLING	3/16OD CXC DIMP-STOP COUPLING
4	9600RS 3/16OD CXC ROLL-STOP COUP W01001	3/16OD CXC ROLL-STOP COUP
5	9600DS 1/4 CXC DIMP-STOP COUPLING W10141	1/4 CXC DIMP-STOP COUPLING
6	9600RS 1/4 CXC ROLL-STOP COUPLING W01003	1/4 CXC ROLL-STOP COUPLING
7	9600R 1/4X3/16OD CXC RED COUPLING W01004	1/4X3/16OD CXC RED COUPLING
8	9600DS 5/16OD CXC DIMP-STOP COUP W10142	5/16OD CXC DIMP-STOP COUP
9	9600RS 5/16OD CXC ROLL-STOP COUP W01006	5/16OD CXC ROLL-STOP COUP
10	9600R 5/16ODX1/4 CXC RED COUPLING	5/16ODX1/4 CXC RED COUPLING

<tbody>
</tbody>

=MID(B3,FIND("/",B3,1)-2,IF(IFERROR(SEARCH(" W",B3),0)=0,(LEN(B3)-(FIND("/",B3,1)-2)+1)+(FIND("/",B3,1)-2),(LEN(B3)-(FIND("/",B3,1)-2)+1)-(FIND("/",B3,1)-2)))

Does this work if the substring at the end is "A01001"?

 

[nithin shettigar]

	B	C
2	9600DS 3/16OD CXC DIMP-STOP COUPLING	3/16OD CXC DIMP-STOP COUPLING
3	9600RS 3/16OD CXC ROLL-STOP COUP W01001	3/16OD CXC ROLL-STOP COUP
4	9600DS 1/4 CXC DIMP-STOP COUPLING W10141	1/4 CXC DIMP-STOP COUPLING
5	9600RS 1/4 CXC ROLL-STOP COUPLING W01003	1/4 CXC ROLL-STOP COUPLING
6	9600R 1/4X3/16OD CXC RED COUPLING v01004	1/4X3/16OD CXC RED COUPLING
7	9600DS 5/16OD CXC DIMP-STOP COUP a10142	5/16OD CXC DIMP-STOP COUP
8	9600RS 5/16OD CXC ROLL-STOP COUP W01006	5/16OD CXC ROLL-STOP COUP
9	9600R 5/16ODX1/4 CXC RED COUPLING	5/16ODX1/4 CXC RED COUPLING

<tbody>
</tbody>

=MID(B2,FIND("/",B2,1)-2,IFERROR(FIND(" ",B2,SEARCH("coup",B2))-FIND("/",B2,1)+2,50))

This may help you

 

[kevinlasvegas]

Thank you for the code. VB is a mystery to me. I never had my "light bulb" moment with VB the way I did with functions & formulas.
I am going to give it a try though. I can at least use it as a learning opportunity. Thank you again and have a great holiday.

 

[István Hirsch]

=MID(B2,FIND("/",B2,1)-2,IFERROR(FIND(" ",B2,SEARCH("coup",B2))-FIND("/",B2,1)+2,50))

This may help you

This really may help the OP if your assumptions are valid.

 

[Rick Rothstein]

Thank you for the code. VB is a mystery to me. I never had my "light bulb" moment with VB the way I did with functions & formulas.
I am going to give it a try though. I can at least use it as a learning opportunity. Thank you again and have a great holiday.

I am not sure how far back (message-wise) you have gone, but since there has been activity since I posted them, I don't want you to overlook Messages #8 and #9 as I think they will provide general solutions to what I think your full set of data looks like.

 

[kevinlasvegas]

I am not sure how far back (message-wise) you have gone, but since there has been activity since I posted them, I don't want you to overlook Messages #8 and #9 as I think they will provide general solutions to what I think your full set of data looks like.

Appreciative of the"heads-up".
I will have to make some choices here.
This can be accomplished with a series of columns using:
=MID(I2,FIND(" ",I2)+1,256)
=LEFT(V2,FIND(" W",V2&" W")-1
=LEFT(W2,FIND(" A",W2&" A")-1)

I can do this for each prefix I find after reviewing the data during prep. From what I have seen it is not more that 2 or 3 different letters on any one source sheet.

I am sure though that these can be nested into one if someone could help get the "()" and "," in the right place.
I am sure we need an AND, IF, with return value of cell if not found.
Thanks again to all

 

[Rick Rothstein]

Appreciative of the"heads-up".
I will have to make some choices here.
This can be accomplished with a series of columns using:
=MID(I2,FIND(" ",I2)+1,256)
=LEFT(V2,FIND(" W",V2&" W")-1
=LEFT(W2,FIND(" A",W2&" A")-1)

I can do this for each prefix I find after reviewing the data during prep. From what I have seen it is not more that 2 or 3 different letters on any one source sheet.

I am sure though that these can be nested into one if someone could help get the "()" and "," in the right place.
I am sure we need an AND, IF, with return value of cell if not found.

You don't want to try the single formula I posted in Message #9?

 

[kevinlasvegas]

That was the one I needed.
Copied down 628 lines in one of my price lists and it worked like a pro.
I am in debt to the group.
Lot of pressure on me and no time for trial and error.
With this solution I should be clear. And I can tweak it for similar situations.
Most of the price lists I have to clean up do this to their data.
Everyone below the tier of Architect in the construction industry needs to get a clue and learn about data management.
Happy Holidays and Thank you to all who contributed I am going to save all solutions offered in my "vault"

 

[LockeGarmin]

Whoops! Looks like I need to make a correction to my original Function due to my silly mistake:

Function RegexReplace(Source As String, Pattern As String, Replacement As String, Optional IgnoreCase As Boolean) As String

Static Regex As Object

  If Regex Is Nothing Then
    Set Regex = CreateObject("VBScript.Regexp")
    Regex.Global = True
  End If
  
  Regex.Pattern = Pattern
  Regex.IgnoreCase = IgnoreCase
  
  RegexReplace = Regex.Replace(Source, Replacement)

End Function

Glad you got it figured out! If you find yourself doing a lot of text manipulation I would definitely recommend looking into Regular Expressions for UDFs. I don't typically recommend developing UDFs for really narrow purposes though in your situation I can certainly understand the necessity. If you don't do a lot of text manipulation than I wouldn't recommend getting into Regular Expressions because they can have a little bit of a learning curve. Personally I haven't used LEFT, MID, RIGHT and the like in a long time because developing the necessary pattern is usually easy enough rather than combining LEFT with FIND to get just the pattern you are looking for.

At the end of the day though, make sure you include the corrected function in your collection if you end up wanting to save it.

 

[AlKey]

Maybe this

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND(" ",A1),"")," ",REPT(" ",99),4),99))

Excel 2016 (Windows) 32 bit
	A	B
1	9600DS 3/16OD CXC DIMP-STOP COUPLING	3/16OD CXC DIMP-STOP COUPLING
2	9600RS 3/16OD CXC ROLL-STOP COUP W01001	3/16OD CXC ROLL-STOP COUP
3	9600DS 1/4 CXC DIMP-STOP COUPLING W10141	1/4 CXC DIMP-STOP COUPLING
4	9600RS 1/4 CXC ROLL-STOP COUPLING W01003	1/4 CXC ROLL-STOP COUPLING
5	9600R 1/4X3/16OD CXC RED COUPLING W01004	1/4X3/16OD CXC RED COUPLING
6	9600DS 5/16OD CXC DIMP-STOP COUP W10142	5/16OD CXC DIMP-STOP COUP
7	9600RS 5/16OD CXC ROLL-STOP COUP W01006	5/16OD CXC ROLL-STOP COUP
8	9600R 5/16ODX1/4 CXC RED COUPLING	5/16ODX1/4 CXC RED COUPLING
Sheet2