# Finding string within string

I need to get part of a string within a string in a cell.

The whole string is for example: 114567     5174     M11001

I want the middle part 5174.

I know that there are several ways to do this but I want the smartest :-)

The string can vary in length, but the middle part is always surrounded by 5 blanks.
###### Who is Participating?

Software DeveloperCommented:
This will work with the middle string varying:

=MID(A1;(FIND(" ";A1;1)+5);(FIND(" ";A1;(FIND(" ";A1;1)+5)))-(FIND(" ";A1;1)+5))
0

Software DeveloperCommented:
Try the following formula:

=MID(A1;FIND(" ";A1)+5;LENGTH(A1) - FIND("-";A1;FIND("-";A1))-15)
0

Commented:
Perhaps?

=MID(A1,FIND("     ",A1)+5,FIND("^^",SUBSTITUTE(A1,"     ","^^",2))-FIND("     ",A1)-5)
0

Software DeveloperCommented:

=MID(A1;FIND(" ";A1)+5;LENGTH(A1) - FIND(" ";A1;FIND(" ";A1))-15)
0

PresidentCommented:
Is it always 4 Numbers surrounded by 5 blanks?

=MID(A1,FIND("     ",A1),9)

EDITED:  To remove Spaces:

=MID(A1,FIND("     ",A1)+5,4)
0

PresidentCommented:
Got to love those Euro formulas!

Alex's formula with commas:

=MID(A1,(FIND(" ",A1,1)+5),(FIND(" ",A1,(FIND(" ",A1,1)+5)))-(FIND(" ",A1,1)+5))
0

Software DeveloperCommented:
Thanks ThinkSpaceSolutions ;-)
Sorry, my Excel version is a German one :-(
0

Commented:
You can use Text to columns on Data tab.
Sorry, I don't know how it is named in German version of Excel.
Select your cells, start master. On first page select - Delimited, press Next.
On next page select space as delimiter and mark "Treat consequtive delimiters as one"
You will have 3 separate columns with parts of your string
0

PresidentCommented:
Not a problem!  If the OP didn't support the semicolons, I didn't want him to bypass your suggestion/solution if it 'didn't work'.
0

Commented:
Hi,

pls try
``````=TEIL(A1;(FINDEN(" ";A1;1)+5);(FINDEN(" ";A1;(FINDEN(" ";A1;1)+5)))-(FINDEN(" ";A1;1)+5))
``````
Regards
0

Software DeveloperCommented:
Hello/Hallo Rgonzo1971:
That's exactly what I posted (German version) ;-)

Viele Grüße
Alex
0

Commented:
@Alex I wasn't attentive enough I thought the answer should be in the german version

Oops
0

Commented:
If you are amenable to a VBA solution, you could try Regular expressions, as explained in my article here.

1) Add this function to a regular VBA module:

``````Function RegExpFindSubmatch(LookIn As String, PatternStr As String, Optional MatchPos, _
Optional SubmatchPos, Optional MatchCase As Boolean = True, _
Optional MultiLine As Boolean = False)

' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
' as long as you properly credit and attribute authorship and the URL of where you
' found the code

' This function relies on the VBScript version of Regular Expressions, and thus some of
' the functionality available in Perl and/or .Net may not be available.  The full extent
' of what functionality will be available on any given computer is based on which version
' of the VBScript runtime is installed on that computer

' This function uses Regular Expressions to parse a string (LookIn), and return "submatches"
' from the various matches to a pattern (PatternStr).  In RegExp, submatches within a pattern
' are defined by grouping portions of the pattern within parentheses.

' Use MatchPos to indicate which match you want:
' MatchPos omitted               : function returns results for all matches
' MatchPos = 1                   : the first match
' MatchPos = 2                   : the second match
' MatchPos = <positive integer>  : the Nth match
' MatchPos = 0                   : the last match
' MatchPos = -1                  : the last match
' MatchPos = -2                  : the 2nd to last match
' MatchPos = <negative integer>  : the Nth to last match

' Use SubmatchPos to indicate which match you want:
' SubmatchPos omitted               : function returns results for all submatches
' SubmatchPos = 1                   : the first submatch
' SubmatchPos = 2                   : the second submatch
' SubmatchPos = <positive integer>  : the Nth submatch
' SubmatchPos = 0                   : the last submatch
' SubmatchPos = -1                  : the last submatch
' SubmatchPos = -2                  : the 2nd to last submatch
' SubmatchPos = <negative integer>  : the Nth to last submatch

' The return type for this function depends on whether your choice for MatchPos is looking for
' a single value or for potentially many.  All arrays returned by this function are zero-based.
' When the function returns a 2-D array, the first dimension is for the matches and the second
' dimension is for the submatches
' MatchPos omitted, SubmatchPos omitted: 2-D array of submatches for each match.  First dimension
'                                        based on number of matches (0 to N-1), second dimension
'                                        based on number of submatches (0 to N-1)
' MatchPos omitted, SubmatchPos used   : 2-D array (0 to N-1, 0 to 0) of the specified submatch
'                                        from each match
' MatchPos used, SubmatchPos omitted   : 2-D array (0 to 0, 0 to N-1) of the submatches from the
'                                        specified match
' MatchPos used, SubmatchPos used      : String with specified submatch from specified match

' For any submatch that is not found, the function treats the result as a zero-length string

' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).

' If you use this function in Excel, you can use range references for any of the arguments.
' If you use this in Excel and return the full array, make sure to set up the formula as an
' array formula.  If you need the array formula to go down a column, use TRANSPOSE()

' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
' where a large number of calls to this function are made, making RegX a static variable that
' preserves its state in between calls significantly improves performance

Static RegX As Object
Dim TheMatches As Object
Dim Mat As Object
Dim Counter As Long
Dim SubCounter As Long

' Evaluate MatchPos.  If it is there, it must be numeric and converted to Long

If Not IsMissing(MatchPos) Then
If Not IsNumeric(MatchPos) Then
RegExpFindSubmatch = ""
Exit Function
Else
MatchPos = CLng(MatchPos)
End If
End If

' Evaluate SubmatchPos.  If it is there, it must be numeric and converted to Long

If Not IsMissing(SubmatchPos) Then
If Not IsNumeric(SubmatchPos) Then
RegExpFindSubmatch = ""
Exit Function
Else
SubmatchPos = CLng(SubmatchPos)
End If
End If

' Create instance of RegExp object

If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = PatternStr
.Global = True
.IgnoreCase = Not MatchCase
.MultiLine = MultiLine
End With

' Test to see if there are any matches

If RegX.Test(LookIn) Then

' Run RegExp to get the matches, which are returned as a zero-based collection

Set TheMatches = RegX.Execute(LookIn)

' If MatchPos is missing, user either wants array of all the submatches for each match, or an
' array of all the specified submatches for each match.  Build it and assign it as the
' function's return value

If IsMissing(MatchPos) Then

' Return value is a 2-D array of all the submatches for each match

If IsMissing(SubmatchPos) Then
For Counter = 0 To TheMatches.Count - 1
Set Mat = TheMatches(Counter)

' To determine how many submatches there are we need to first evaluate a match.  That
' is why we redim the array inside the for/next loop

If Counter = 0 Then
ReDim Answer(0 To TheMatches.Count - 1, 0 To Mat.Submatches.Count - 1) As String
End If

' Loop through the submatches and populate the array.  If the Nth submatch is not
' found, RegExp returns a zero-length string

For SubCounter = 0 To UBound(Answer, 2)
Next
Next

' Return value is a 2-D array of the specified submatch for each match.

Else
For Counter = 0 To TheMatches.Count - 1
Set Mat = TheMatches(Counter)

' To determine how many submatches there are we need to first evaluate a match.  That
' is why we redim the array inside the for/next loop.  If SubmatchPos = 0, then we want
' the last submatch.  In that case reset SubmatchPos so it equals the submatch count.
' Negative number indicates Nth to last; convert that to applicable "positive" position

If Counter = 0 Then
ReDim Answer(0 To TheMatches.Count - 1, 0 To 0) As String
Select Case SubmatchPos
Case Is > 0: 'no adjustment needed
Case 0, -1: SubmatchPos = Mat.Submatches.Count
Case Is < -Mat.Submatches.Count: SubmatchPos = -SubmatchPos
Case Else: SubmatchPos = Mat.Submatches.Count + SubmatchPos + 1
End Select
End If

' Populate array with the submatch value.  If the submatch value is not found, or if
' SubmatchPos > the count of submatches, populate with a zero-length string

If SubmatchPos <= Mat.Submatches.Count Then
Answer(Counter, 0) = Mat.Submatches(SubmatchPos - 1)
Else
End If
Next
End If

' User wanted the info associated with the Nth match (or last match, if MatchPos = 0)

Else

' If MatchPos = 0 then make MatchPos equal the match count.  If negative (indicates Nth
' to last), convert to equivalent position.

Select Case MatchPos
Case Is > 0: 'no adjustment needed
Case 0, -1: MatchPos = TheMatches.Count
Case Is < -TheMatches.Count: MatchPos = -MatchPos
Case Else: MatchPos = TheMatches.Count + MatchPos + 1
End Select

' As long as MatchPos does not exceed the match count, process the Nth match.  If the
' match count is exceeded, return a zero-length string

If MatchPos <= TheMatches.Count Then
Set Mat = TheMatches(MatchPos - 1)

' User wants a 2-D array of all submatches for the specified match; populate array.  If
' a particular submatch is not found, RegExp treats it as a zero-length string

If IsMissing(SubmatchPos) Then
ReDim Answer(0 To 0, 0 To Mat.Submatches.Count - 1)
For SubCounter = 0 To UBound(Answer, 2)
Next

' User wants a single value

Else

' If SubmatchPos = 0 then make it equal count of submatches.  If negative, this
' indicates Nth to last; convert to equivalent positive position

Select Case SubmatchPos
Case Is > 0: 'no adjustment needed
Case 0, -1: SubmatchPos = Mat.Submatches.Count
Case Is < -Mat.Submatches.Count: SubmatchPos = -SubmatchPos
Case Else: SubmatchPos = Mat.Submatches.Count + SubmatchPos + 1
End Select

' If SubmatchPos <= count of submatches, then get that submatch for the specified
' match.  If the submatch value is not found, or if SubmathPos exceeds count of
' submatches, return a zero-length string.  In testing, it appeared necessary to

If SubmatchPos <= Mat.Submatches.Count Then
RegExpFindSubmatch = CStr(Mat.Submatches(SubmatchPos - 1))
Else
RegExpFindSubmatch = ""
End If
End If
Else
RegExpFindSubmatch = ""
End If
End If

' If there are no matches, return empty string

Else
RegExpFindSubmatch = ""
End If

Cleanup:
' Release object variables
Set Mat = Nothing
Set TheMatches = Nothing

End Function
``````

2) Use it in a formula like this:

=RegExpFindSubmatch(A2,"( {5})([^ ]+)( {5})",1,2)

That will extract the first bit of text that is both preceded by and followed by five spaces.
0

Author Commented:
Since I don't want a VBA solution I'm choosing this as the best answer. Thanks all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.