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]

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.

Are spaces, forward slashes and dashes the only non-alphanumeric characters that could be in the text?

 

[LGFN1]

Things can change with time, but as of now I think this is all I got. Either way, I understand that once I get the hang of the formula, I can manipulate it to match my needs. Correct?

 

[Michael M]

Would this not do as required

Excel 2010
	A	B
1	GH MFT85E98/12	MFT85E98
2	GH-MFT85E98-12	MFT85E98
3	GH/MFT85E98/12	MFT85E98
4	GH MFT85E98 12	MFT85E98
5	GH MFT85E98	MFT85E98
6	GH-MFT85E98	MFT85E98
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,4,8)

 

[Rick Rothstein]

Michael raises a good point... is your data as regular as you show, namely, two characters followed by a non-alphanumeric character followed by the eight characters you want (what follows that would be immaterial)?

 

[LGFN1]

Oops, my mistake, I should've been more clear. Sorry! It's not always two characters in the beginning, it's not always eight characters in middle, and it's not always two characters at the end. All this varies greatly from one value to another.

 

[Michael M]

Okay, then a few more examples would help....and just to confirm it is ALWAYS MFT85E98 to be extracted ??

 

[Michael M]

I'm out on the road, so won't have Excel for a while, but if you need to extract 3 letters followed by 2 digits, 1 letter 2 digits...try this
Rick will probably provide a non Regex solution that will be quicker but it will be up to you

Function PN(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "([A-z]{3}[0-9]{2}[A-z][0-9]{2})"
  If .Test(s) Then PN = .Execute(s)(0).SubMatches(0)
End With
End Function

 

[Rick Rothstein]

Oops, my mistake, I should've been more clear. Sorry! It's not always two characters in the beginning, it's not always eight characters in middle, and it's not always two characters at the end. All this varies greatly from one value to another.

Okay, give this formula a try...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/"," "),"-"," ")," ",REPT(" ",99)),100,99))

 

[LGFN1]

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?