Forum Discussion

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 lengt...

Find Provider Help.xlsx11 KB

Formulas and Functions

Macros and VBA

office 365

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.

Find Provider Help.xlsm22 KB

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.

Find Provider Help.xlsm22 KB

calof1
Iron Contributor
Mar 22, 2020
hiSubodh_Tiwari_sktneer

Thank you again for your assistance, very much appreciated. Works perfectly.

Many thanks,
- Subodh_Tiwari_sktneer
  Silver Contributor
  Mar 22, 2020
  You're welcome calof1! Glad it worked as desired.
  - calof1
    Iron Contributor
    Mar 22, 2020
    HiSubodh_Tiwari_sktneer
    
    I have tried replicating this to my spreadsheet. The output i wish to place the service provider is Column X, looking at the macro i believe i need to update the below section from to say 24 (new Column number).
    
    cel.Offset(0, 2)
    
    Given the information is still column D, i am guessing i change the below from A to D?
    Set rng = Range("A2:A" & lr)
    With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "[A-Z]{3,}"
    
    Would i need to update anything else?
    
    Many thanks,