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]

Could you attach a list with the desired result for each sample provided?

 

[Rick Rothstein]

A couple of questions...

Is your leader always a 4-digit number followed by one or two letters as shown in your examples?

Is your trailer, when present, always an A or W followed by 5 digits as shown in your examples?

 

[LockeGarmin]

Using this UDF will give you the ability to use a Regular Expression Replace function in Excel:

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

Static Regex As Object
  
  If Not 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

Then you can use this formula in B1 (assuming your first cell is in A1) and copy down as needed to remove the qualifying leaders and trailers:

=RegexReplace(A1,"^\s*\S+\s+|\s+[AaWw]\d+\s*$","")

 

[kevinlasvegas]

Hello and thank you.

3/16OD CXC DIMP-STOP COUPLING

3/16OD CXC ROLL-STOP COUP

1/4 CXC DIMP-STOP COUPLING

1/4 CXC ROLL-STOP COUPLING

1/4X3/16OD CXC RED COUPLING

5/16OD CXC DIMP-STOP COUP

5/16OD CXC ROLL-STOP COUP

5/16ODX1/4 CXC RED COUPLING That is how the data should look. I can clean it up if I can get it to this state. The LEADER is of variable length and ALPHANUMERIC configurations with and without hyphens. There is ALWAYS a space after it. I have reviewed over 7300 lines and that is one constant. The TRAILER is also of variable length of 4,5 or 6 ALPHANUMERIC characters but it ALWAYS has a space before it. So extract every thing after the first space and before the last space if the last space is followed by a "W" or an "A" with a series of numbers after it. The "W" and "A" are always uppercase. I think there is enough to grab onto I simply am lost on the syntax. I truly appreciate any and all direction, guidance and input. Thank you. Kevin in Las Vegas

<colgroup><col></colgroup><tbody>
</tbody>

 

[LockeGarmin]

Excel isn't as strong when it comes to text parsing and pattern matching, so I'm not sure you'll find much outside of using a UDF like I had suggested above. Getting the LEADER is pretty simple but the TRAILER is going to be pretty difficult with just regular Excel functions. I'd be pretty impressed if someone can come up with a solution using only Excel functions that doesn't span multiple lines.

 

[Rick Rothstein]

Here is another UDF (user defined function) you can consider...

[table="width: 500"]
[tr]
	[td]Function Description(ByVal S As String) As String
  Dim Words() As String
  Words = Split(S & " ")
  Words(0) = ""
  If Words(UBound(Words)) Like "[AW]*" Then Words(UBound(Words)) = ""
  Description = Trim(Join(Words))
End Function[/td]
[/tr]
[/table]

To use this UDF, simply give it the cell reference to the text you want to parse. So, if there is text in cell A2, put this formula in cell B2 and copy it down as needed...

=Description(A2)


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Description just like it was a built-in Excel function (see example usage above).

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

Here is another UDF (user defined function) you can consider...

[table="width: 500"]
[tr]
	[td]Function Description(ByVal S As String) As String
  Dim Words() As String
  Words = Split(S & " ")
  Words(0) = ""
  If Words(UBound(Words)) Like "[AW]*" Then Words(UBound(Words)) = ""
  Description = Trim(Join(Words))
End Function[/td]
[/tr]
[/table]

To use this UDF, simply give it the cell reference to the text you want to parse. So, if there is text in cell A2, put this formula in cell B2 and copy it down as needed...

=Description(A2)


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Description just like it was a built-in Excel function (see example usage above).

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

The above UDF is not "perfect" as written... use the following more robust version instead...

[table="width: 500"]
[tr]
	[td]Function Description(ByVal S As String) As String
  Dim LastWord As String, Words() As String
  If Len(Trim(S)) = 0 Then Exit Function
  Words = Split(S)
  Words(0) = ""
  LastWord = Words(UBound(Words))
  If LastWord Like "[AW]" & String(Len(LastWord) - 1, "#") Then Words(UBound(Words)) = ""
  Description = Trim(Join(Words))
End Function[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Excel isn't as strong when it comes to text parsing and pattern matching, so I'm not sure you'll find much outside of using a UDF like I had suggested above. Getting the LEADER is pretty simple but the TRAILER is going to be pretty difficult with just regular Excel functions. I'd be pretty impressed if someone can come up with a solution using only Excel functions that doesn't span multiple lines.

Here is (as you predicted) a somewhat lengthy formula that seems to work, although it is not as robust as the UDF that I posted in Message #8... it can be fooled by a last word starting with A or W followed by two or more digits followed by one or more non-digits (such as A12BEAM).

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",300)),300,(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-AND(OR(LEFT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))))={"A","W"}),ISNUMBER(0+MID(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),2,2))))*300))

 

[nithin shettigar]

	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)))