Group Function

[MikeDBMan]

In other languages I have worked with, I could use a group() function that would pick out a subset of text within a string. Group("1234 5678 9101112 13141516",2) ="5678" because 5678 is the second "grouping" of data in the string. Is there a similar function in VBA?

 

[MrKowz]

I don't believe there is one built into VBA, but I built a quick UDF that emulates it. If you need an optional spot to choose what delimiter to use (space, comma, semicolon, etc), lemme know:

Public Function GroupFind(iString As String, GrpNum As Long)
Dim i As Long
Dim tempstring As String
If GrpNum = 1 Then
   GroupFind = Left(iString, Application.Find(" ", iString))
Else
   tempstring = iString
   For i = 2 To GrpNum
      tempstring = Right(tempstring, Len(tempstring) - Application.Find(" ", tempstring))
   Next i
   If IsError(Application.Find(" ", tempstring)) Then
      GroupFind = tempstring
   Else
      GroupFind = Left(tempstring, Application.Find(" ", tempstring))
   End If
End If
End Function

Excel Workbook
	A
1	1234 5678 9101112 13141516
2	9101112
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A2	=groupfind(A1,3)

 

[MikeDBMan]

That is exactly what I wanted. I am not going to use it as a UDF but certainly could. I need it for coding in VBA. Thanks!

 

[MrKowz]

You can use UDFs in VBA as well

 

[MrKowz]

Also, I was able to work out a version where you can choose what delimiter to use. if you do not declare the delimiter, then it will default to a space:

Public Function GroupFind(iString As String, GrpNum As Long, Optional delim As Variant)
Dim i As Long
Dim tempstring As String
If IsMissing(delim) Then delim = Chr(32)
If GrpNum = 1 Then
   GroupFind = Left(iString, Application.Find(delim, iString) - 1)
Else
   tempstring = iString
   For i = 2 To GrpNum
      tempstring = Right(tempstring, Len(tempstring) - Application.Find(delim, tempstring))
   Next i
   If IsError(Application.Find(delim, tempstring)) Then
      GroupFind = tempstring
   Else
      GroupFind = Left(tempstring, Application.Find(delim, tempstring) - 1)
   End If
End If
End Function

Excel Workbook
	A
1	1234 5678 9101112 13141516
2	9101112
3
4	1234/5678/9101112/13141516
5	9101112
6
7	1234;5678;9101112;13141516
8	9101112
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A2	=groupfind(A1,3)
A5	=groupfind(A4,3,"/")
A8	=groupfind(A7,3,";")

Note: This will only work for a delimiter that is 1 character. I'm working on figuring out how to handle it if the delimiter is more than 1 character.

 

[MikeDBMan]

You are correct. It is still a UDF even when used only in Excel instead of "topside" in the worksheet itself. I am going to use your latest version with the optional delimiter which is extremely powerful. Thanks for creating it for me!

 

[MrKowz]

GOT IT! And glad it works for you. I love taking on fun projects like this... always ends up giving me ideas for future projects (or at least it teaches me something along the way!)

Public Function GroupFind(iString As String, GrpNum As Long, Optional delim As Variant)
Dim i As Long
Dim tempstring As String
If IsMissing(delim) Then delim = Chr(32)
If GrpNum = 1 Then
   GroupFind = Left(iString, Application.Find(delim, iString) - 1)
Else
   tempstring = iString
   For i = 2 To GrpNum
      tempstring = Right(tempstring, Len(tempstring) - Application.Find(delim, tempstring))
   Next i
   If IsError(Application.Find(delim, tempstring)) Then
      GroupFind = Right(tempstring, Len(tempstring) - Len(delim) + 1)
   Else
      GroupFind = Left(Right(tempstring, Len(tempstring) - Len(delim) + 1), Application.Find(delim, tempstring) - Len(delim))
   End If
End If
End Function

Excel Workbook
	A
7	1234;5678;9101112;13141516
8	9101112
9
10	1234DELIMTHIS5678DELIMTHIS9101112DELIMTHIS13141516
11	13141516
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A8	=groupfind(A7,3,";")
A11	=groupfind(A10,4,"DELIMTHIS")