Extract 4 character string

pto160

Active Member
Joined
Feb 1, 2009
Messages
317
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,575
Office Version
2010
Platform
Windows
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...
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,575
Office Version
2010
Platform
Windows
Sure, the "batch" requirement is there only because you requested it. Here is the code from Message #35 modified to remove the "batch" requirement...
Code:
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...
Code:
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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

Active Member
Joined
Feb 1, 2009
Messages
317
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,086,111
Messages
5,387,896
Members
402,086
Latest member
vlmedia

Some videos you may like

This Week's Hot Topics

Top