Forum Discussion

calof1's avatar
calof1
Iron Contributor
Mar 19, 2020
Solved

Help to use TRIM or Left & RIGHT to extract TEXT

Hi, 

 

I have a list of data files, the short form code of the service provider is contained in the file name. I wish to use this to then sort and follow up by service provider. The file name lengths can differ.

 

Can someone please assist in helping extract the service provider code from the filename?

 

Thank you kindly for any assistance.

admin
BI & Data Analysis
Charting
developer
excel
Excel for web
Excel on Mac
Formulas and Functions
Macros and VBA
office 365
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Mar 19, 2020

    calof1 

    You may try the following macro to get the service providers in column B.

    Sub FindServiceProvider()
Dim lr      As Long
Dim rng     As Range
Dim cel     As Range
Dim Matches As Object

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "[A-Z]{3,}"
    For Each cel In rng
        If .Test(cel) Then
            Set Matches = .Execute(cel.Value)
            cel.Offset(0, 1).Value = Matches(0)
        End If
    Next cel
End With

Application.ScreenUpdating = True
End Sub

     

    In the attached, click the button called "Get Service Providers" to run the code.

     

     

9 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Mar 19, 2020

    calof1 

    You may try the following macro to get the service providers in column B.

    Sub FindServiceProvider()
Dim lr      As Long
Dim rng     As Range
Dim cel     As Range
Dim Matches As Object

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "[A-Z]{3,}"
    For Each cel In rng
        If .Test(cel) Then
            Set Matches = .Execute(cel.Value)
            cel.Offset(0, 1).Value = Matches(0)
        End If
    Next cel
End With

Application.ScreenUpdating = True
End Sub

     

    In the attached, click the button called "Get Service Providers" to run the code.

     

     

Resources