Extract 4 character string

[pto160]

I'm trying to extract a 4 character string that can be numbers or a text string. Here is the example.

Sheet1

	A	B
1		Result
2	abc rtyidf 1034 567	1034
3	fg abcd 789 1464654	abcd
4	erd f rtyu	rtyu

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 126px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

How do you do it?

 

[pto160]

Peter_SSs, duly noted about providing more concrete examples. It probably is a good idea to provide examples that are closer to the type of data you use at work. Thanks for the tip.

 

[Rick Rothstein]

Rick, I also used your UDF and it works very good.

Is there a way to change it so it extracts the character string without looking for batch as the starting point?

Sure, the "batch" requirement is there only because you requested it. Here is the code from Message #35 modified to remove the "batch" requirement...

Function Chars(S As String, Num As Long) As String
  Dim X As Long, Txt() As String
  Txt = Split(S)
  For X = 0 To UBound(Txt)
    If Txt(X) Like Application.Rept("[A-Za-z0-9]", Num) Then
      Chars = Txt(X)
      Exit Function
    End If
  Next
End Function

 

[Rick Rothstein]

Sure, the "batch" requirement is there only because you requested it. Here is the code from Message #35 modified to remove the "batch" requirement...

Function Chars(S As String, Num As Long) As String
  Dim X As Long, Txt() As String
  Txt = Split(S)
  For X = 0 To UBound(Txt)
    If Txt(X) Like Application.Rept("[A-Za-z0-9]", Num) Then
      Chars = Txt(X)
      Exit Function
    End If
  Next
End Function

Actually, the above code can be simplified slightly...

Function Chars(S As String, Num As Long) As Variant
  For Each Chars In Split(S)
    If Chars Like Application.Rept("[A-Za-z0-9]", Num) Then Exit Function
  Next
  Chars = ""
End Function

 

[István Hirsch]

Another version for the "batch"-less case:

=MID(" "&A2&" ",SEARCH(" ???? "," "&A2&" ",MATCH(4,LEN(SUBSTITUTE(MID(" "&A2&" ",SEARCH(" ???? "," "&A2&" ",ROW($1:$150)),6)," ","")),0))+1,4)

 

[pto160]

Rick, I'll be using both UDF'S. The batch string has 2 4 character word or numbers so it will help me get the second word or number. The other UDF will be for strings that don't have batch.

The UDF'S work great. This will save me a lot of time. It's unbelievable what Excel can do.

Thanks so much for the formula István Hirsch. It works great.

 

[István Hirsch]

You are welcome, thanks for the feedback.