Extract Text using Delimiter which is Dynamic

[ragav_in]

Hi All,

I have 3 worksheets in excel whose names are tv_ABCD_ReportName, tv_EFGH_IJKL_ReportName & tv_MNOP_QRSTUV_WXYZ_ReportName. What I am trying to do is to extract text before the underscores based on certain criteria. In the above reports ABCD, EFGH & MNOP_QRSTUV are table names. My task is to extract only the table names from the worksheet name so that the output is as below. Note the number of underscores "_", is dynamic in the report name, and its always the characters before the 1st & 2nd underscore is Table Name (as shown in bold typeface). the number of characters of table name is also dynamic and so is the underscore in the report names.
tv_ABCD_ReportName = ABCD
tv_EFGH_IJKL_ReportName = EFGH_IJKL
tv_MNOP_QRSTUV_WXYZ_ReportName = MNOP_QRSTUV (note that WXYZ) is not included as a table name

In short, the code I am looking for is to check the report name for how many underscores it has. If it has 1, then the characters before the 1st underscore is to be retrieved. If it has 2 underscore, the characters before the 2nd underscore has to be retrieved. If it has 3 underscores, then also only the characters before the 2nd underscore has to be retrieved as table name

I have the following code for this, but I am unable to get the expected output. I am not sure which piece I am missing. May I request you to look into this and see if you can refine my code, so that I get my output. Note: the code errors out if the sheetname has only 1 underscore, if it has 2, it works fine.

Sub getrepname()
Dim Rpt, myRpt, myTbl As String
Rpt = ActiveSheet.Name
'<-- Check for your string to not throw error
If InStr(Rpt, "_") > 0 Then
    myRpt = Split(Rpt, "_")(0) & "_" & Split(Rpt, "_")(1) & "_" & Split(Rpt, "_")(2)
    myTbl = Right(myRpt, Len(myRpt) - 2)
    MsgBox myRpt, , "Report Name"
    MsgBox myTbl, , "Table Name"
End If
End Sub

Thanks in advance for your support.

Thanks
ragav_in

 

[Fluff]

How about

Sub getrepname()
Dim Rpt As String, myTbl As String
Dim Sp As Variant
Rpt = ActiveSheet.Name
'<-- Check for your string to not throw error
If InStr(Rpt, "_") > 0 Then
   Sp = Split(Rpt, "_")
   If UBound(Sp) = 1 Then
      myTbl = Sp(0)
   ElseIf UBound(Sp) = 2 Then
      myTbl = Sp(1)
   Else
      myTbl = Sp(1) & "_" & Sp(2)
   End If

    MsgBox myTbl, , "Table Name"
End If
End Sub

 

[ragav_in]

Hi All,

I have 3 worksheets in excel whose names are tv_ABCD_ReportName, tv_EFGH_IJKL_ReportName & tv_MNOP_QRSTUV_WXYZ_ReportName. What I am trying to do is to extract text before the underscores based on certain criteria. In the above reports ABCD, EFGH & MNOP_QRSTUV are table names. My task is to extract only the table names from the worksheet name so that the output is as below. Note the number of underscores "_", is dynamic in the report name, and its always the characters before the 1st & 2nd underscore is Table Name (as shown in bold typeface). the number of characters of table name is also dynamic and so is the underscore in the report names.
tv_ABCD_ReportName = ABCD
tv_EFGH_IJKL_ReportName = EFGH_IJKL
tv_MNOP_QRSTUV_WXYZ_ReportName = MNOP_QRSTUV (note that WXYZ) is not included as a table name

In short, the code I am looking for is to check the report name for how many underscores it has. If it has 1, then the characters before the 1st underscore is to be retrieved. If it has 2 underscore, the characters before the 2nd underscore has to be retrieved. If it has 3 underscores, then also only the characters before the 2nd underscore has to be retrieved as table name

I have the following code for this, but I am unable to get the expected output. I am not sure which piece I am missing. May I request you to look into this and see if you can refine my code, so that I get my output. Note: the code errors out if the sheetname has only 1 underscore, if it has 2, it works fine.

Sub getrepname()
Dim Rpt, myRpt, myTbl As String
Rpt = ActiveSheet.Name
'<-- Check for your string to not throw error
If InStr(Rpt, "_") > 0 Then
    myRpt = Split(Rpt, "_")(0) & "_" & Split(Rpt, "_")(1) & "_" & Split(Rpt, "_")(2)
    myTbl = Right(myRpt, Len(myRpt) - 2)
    MsgBox myRpt, , "Report Name"
    MsgBox myTbl, , "Table Name"
End If
End Sub

Thanks in advance for your support.

Thanks
ragav_in

Sorry that I had provided a wrong naming convention in the above post; correcting it here. Apologies for the same.
tvABCD_ReportName, tvEFGH_IJKL_ReportName & tvMNOP_QRSTUV_WXYZ_ReportName

Expected output
tvABCD_ReportName = ABCD
tvEFGH_IJKL_ReportName = EFGH_IJKL
tvMNOP_QRSTUV_WXYZ_ReportName = MNOP_QRSTUV (note that WXYZ) is not included as a table name

 

[Fluff]

In that case how about

Sub getrepname()
Dim Rpt As String, myTbl As String
Dim Sp As Variant
Rpt = ActiveSheet.Name
'<-- Check for your string to not throw error
If InStr(Rpt, "_") > 0 Then
   Sp = Split(Rpt, "_")
   If UBound(Sp) = 1 Then
      myTbl = Mid(Sp(0), 3)
   Else
      myTbl = Mid(Sp(0), 3) & "_" & Sp(1)
   End If

    MsgBox myTbl, , "Table Name"
End If
End Sub

 

[ragav_in]

How about

Sub getrepname()
Dim Rpt As String, myTbl As String
Dim Sp As Variant
Rpt = ActiveSheet.Name
'<-- Check for your string to not throw error
If InStr(Rpt, "_") > 0 Then
   Sp = Split(Rpt, "_")
   If UBound(Sp) = 1 Then
      myTbl = Sp(0)
   ElseIf UBound(Sp) = 2 Then
      myTbl = Sp(1)
   Else
      myTbl = Sp(1) & "_" & Sp(2)
   End If

    MsgBox myTbl, , "Table Name"
End If
End Sub

Dear Fluff, thanks much for the prompt response. However, when I tried running the code against the 3 below reports, I am getting different output. Also note that the name format I had provided was incorrect, which I corrected, but you had written this before I made the correction. The output is shown below for your reference.

tvWXYZ_FunctionalLocationMain_P - FunctionalLocationMain (Expected is WXYZ)
tvABCD_ReportName - tvABCD (Expected is ABCD)
tvEFGH_IJKL_ReportName - IJKL (Expected is EFGH_IJKL)

Thanks a lot.
ragav_in

 

[Fluff]

See post#4

 

[ragav_in]

In that case how about

Sub getrepname()
Dim Rpt As String, myTbl As String
Dim Sp As Variant
Rpt = ActiveSheet.Name
'<-- Check for your string to not throw error
If InStr(Rpt, "_") > 0 Then
   Sp = Split(Rpt, "_")
   If UBound(Sp) = 1 Then
      myTbl = Mid(Sp(0), 3)
   Else
      myTbl = Mid(Sp(0), 3) & "_" & Sp(1)
   End If

    MsgBox myTbl, , "Table Name"
End If
End Sub

Dear Fluff, thanks for the code, and it provides the details as required... Thanks much for the swift response and I am grateful to you for this.

 

[Fluff]

You're welcome & thanks for the feedback.