Extract text

[LGFN1]

I'm trying to extract a string from a cell.

The said cell may be in either one of the following patterns (always, of course, with different values):

GH MFT85E98/12
GH-MFT85E98-12
GH/MFT85E98/12
GH MFT85E98 12
GH MFT85E98
GH-MFT85E98

and so on.

In other words, it's unpredictable how the value is patterned. What I'm trying to extract is all text between the first non-alphanumeric character and the last non-alphanumeric character (if there is one). So in all the above cases my result should be MFT85E98.

 

[Rick Rothstein]

Thanks, there's just one problem, this extracts from the first non-alphanumeric character to the next one. I need from the first to the last. Is it possible?

Who are you replying to? When you have multiple replies to a question you ask, you should mention who your response is directed to when replying. I will say that in my tests, the formula I posted in Message #9 returns the same values you said you wanted in Message #1... are you getting different results (if so, post some examples so we can refine our code/formulas)?

 

[LGFN1]

Sorry, Rick, I'm referring to your response.

I have lots of them that are, for example, GH-MFT85E98-12/8-5, at such an instance I need MFT85E98-12/8. In other words everything between the first and last non-alphanumeric character.

 

[LGFN1]

I'm in middle of creating a regex pattern for it on RegExr.com (I'm much better at that than Excell), so I think that once I have it I'll probably be able to implement in Excel, correct?

 

[Rick Rothstein]

Sorry, Rick, I'm referring to your response.

I have lots of them that are, for example, GH-MFT85E98-12/8-5, at such an instance I need MFT85E98-12/8. In other words everything between the first and last non-alphanumeric character.

That is completely different than what you suggested your text (patterns) would look like in your original message. There may be a formula solution, but I suspect it would be long and ugly, so I'll give you a UDF (user defined function) instead...

Function Middle(S As String) As String
  Dim X As Long, FirstNonChar As Long, LastNonChar As Long, Temp As String
  Temp = S
  For X = 1 To Len(Temp)
    If Mid(Temp, X, 1) Like "[!A-Za-z0-9]" Then
      FirstNonChar = X
      Exit For
    End If
  Next
  For X = Len(Temp) To 1 Step -1
    If Mid(Temp, X, 1) Like "[!A-Za-z0-9]" Then
      LastNonChar = X
      Exit For
    End If
  Next
  If FirstNonChar = LastNonChar Then
    Middle = Mid(S, FirstNonChar + 1)
  Else
    Middle = Mid(S, FirstNonChar + 1, LastNonChar - FirstNonChar - 1)
  End If
End Function

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 Middle just like it was a built-in Excel function. For example,

=Middle(A1)

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.

 

[LGFN1]

WOW! Thank you so much, Rick!

I've tested it on a lot of instances and most work flawlessly! One minor issue, when it ends with (1), for example, like GROT FRH58HG/FRE (1), the result is FRH58HG/FRE (1, rather than FRH58HG/FRE. Probably my fault, as I didn't specify that I have such as well, sorry.

 

[Michael M]

Misread post

 

[Rick Rothstein]

WOW! Thank you so much, Rick!

I've tested it on a lot of instances and most work flawlessly! One minor issue, when it ends with (1), for example, like GROT FRH58HG/FRE (1), the result is FRH58HG/FRE (1, rather than FRH58HG/FRE. Probably my fault, as I didn't specify that I have such as well, sorry.

Again, this is a pattern that one could not anticipate from your previous message. You must keep in mind when you ask questions on forum that the people trying to help you know absolutely nothing about your data, its "shape", its location, its layout, etc. except for what you tell us... there is no way we can accurately guess at any of those on our own... you must tell us the full range of variation in any of them so that we can properly design solution for you. Basically... if you don't tell us, we don't know; and if we don't know, we can't design for it.

Now, with that said, I have a question... can parentheses appear elsewhere in your text except at the end like these example show? If so, are the considered non-alphanumeric characters in the same way dashes and slashes are?

 

[LGFN1]

I hang my head in shame Sorry, Rick.

Yes, the parentheses is the same as the slash and hyphen.

 

[Rick Rothstein]

I hang my head in shame Sorry, Rick.

Yes, the parentheses is the same as the slash and hyphen.

Give this revised macro a try...

Function Middle(S As String) As String
  Dim X As Long, FirstNonChar As Long, LastNonChar As Long, Temp As String
  Temp = S
  For X = 1 To Len(Temp)
    If Mid(Temp, X, 1) Like "[!A-Za-z0-9]" Then
      FirstNonChar = X
      Exit For
    End If
  Next
  For X = Len(Temp) To 1 Step -1
    If Mid(Temp, X, 1) Like "[!A-Za-z0-9)]" Then
      LastNonChar = X
      Exit For
    End If
  Next
  If FirstNonChar = LastNonChar Then
    Middle = Trim(Mid(S, FirstNonChar + 1))
  Else
    Middle = Trim(Mid(S, FirstNonChar + 1, LastNonChar - FirstNonChar - 1))
  End If
End Function

 

[LGFN1]

AWESOME! Thanks so much, Rick!