Extract Text using Delimiter which is Dynamic

ragav_in

Board Regular
Joined
Feb 13, 2006
Messages
89
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
How about
VBA Code:
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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
In that case how about
VBA Code:
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
 
Upvote 0
Solution
How about
VBA Code:
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
 
Upvote 0
In that case how about
VBA Code:
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top