VBA Code to split cell's text data

Hey Team,

I have a macro that give file location in Column F. I want to split cell's data of column F between last two backslash. First splitted data should go to Column E and last to column A but without .xlsx. Location format would be same for all entire column F.

Also column A is a name and there is a email database in sheet "Email ID data" . We also need to identify email ID for each of entry in column A.

I have attached sample sheet. Please look into this for reference. Please help on this.

Thanks,
Liz

Hi,
Here is macro that
split value in column F
populate column A
Populate column E
populate B with VLOOKUP formula that retrieves data from the other sheet

Sub SplitString()
    Dim cl As Range
    Dim vSplitString As Variant
    Dim intIndex As Integer
    Dim strName As String
    Dim rngEMails As Range
    
    With Worksheets("Email ID Data")
        Set rngEMails = .Range("A1").Resize(.Range("A" & _
                                                   Rows.Count).End(xlUp).Row, 2)
    End With

    For Each cl In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        vSplitString = VBA.Split(cl.Value, "\")
        intIndex = UBound(vSplitString)
        strName = vSplitString(intIndex)
        Range("A" & cl.Row).Value = Left(strName, InStrRev(strName, ".") - 1)    'Name
        Range("E" & cl.Row).Value = vSplitString(intIndex - 1)  'Subject
        Range("B" & cl.Row).FormulaR1C1 = "=VLOOKUP(RC1," & _
            rngEMails.Address(True, True, xlR1C1, True) & ",2,False)"
    Next cl

End Sub

Originally Posted by Liz_Biz

Is it possible to split path like "C:\Documents and Settings\liz\Desktop\INST\UID_August_Baker,Edward D.xlsx" into two parts?
Name (Column A) "Baker,Edward D" and Subject (Column E) "UID_August". Rest condition would be same.

Yes, it's possible but you should specify where to split (e.g. at last underscore)

Originally Posted by Liz_Biz

Also we can't use vlookup as this fails once it search same value again.

I don't understand waht you mean with that. Can you explain further. You can always convert formula to value...

See attached. You don't need macros. Columns A & E contain formulae to do the job for you.

And of course a standard VLOOKUP will get you the email address onto the EMail ID Data sheet - or indeed an Advanced Data Filter.

test this if it works

Sub SplitString()
    Dim cl As Range
    Dim vSplitString As Variant
    Dim strName As String
    Dim rngEMails As Range

    With Worksheets("Email ID Data")
        Set rngEMails = .Range("A1").Resize(.Range("A" & _
                                                   Rows.Count).End(xlUp).Row, 2)
    End With

    For Each cl In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        vSplitString = VBA.Split(cl.Value, "\")
        strName = vSplitString(UBound(vSplitString))
        Range("A" & cl.Row).Value = Mid(strName, InStrRev(strName, "_"), InStrRev(strName, ".") - 1)   'Name
        Range("E" & cl.Row).Value = Left(strName, InStrRev(strName, "_"))  'Subject
        Range("B" & cl.Row).FormulaR1C1 = "=VLOOKUP(RC1," & _
                                          rngEMails.Address(True, True, xlR1C1, True) & ",2,False)"
    
    Next cl
    With ActiveSheet
        .Calculate    'Just in case if calculation is set to manual
        With .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
            .Value = .Value 'Convert to values
        End With
    End With

End Sub

Buran,

I am getting underscore at the end and start of split data (column A & E). Also vlookup is not working due to underscore and .xlsx in column A.

Thanks,
Liz

Sorry, now it should be OK

Sub SplitString()
    Dim cl As Range
    Dim vSplitString As Variant
    Dim strName As String
    Dim rngEMails As Range

    With Worksheets("Email ID Data")
        Set rngEMails = .Range("A1").Resize(.Range("A" & _
                                                   Rows.Count).End(xlUp).Row, 2)
    End With

    With Worksheets("Mail_Data")
        For Each cl In .Range("F2:F" & .Range("F" & Rows.Count).End(xlUp).Row)
            vSplitString = VBA.Split(cl.Value, "\")
            strName = vSplitString(UBound(vSplitString))
            .Range("A" & cl.Row).Value = Mid(strName, InStrRev(strName, "_") + 1, InStrRev(strName, ".") - InStrRev(strName, "_") - 1)    'Name
            .Range("E" & cl.Row).Value = Left(strName, InStrRev(strName, "_") - 1)    'Subject
            .Range("B" & cl.Row).FormulaR1C1 = "=VLOOKUP(RC1," & _
                                               rngEMails.Address(True, True, xlR1C1, True) & ",2,False)"

        Next cl
        .Calculate    'Just in case if calculation is set to manual
        With .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
            .Value = .Value    'Convert to values
        End With
    End With

End Sub

Great, don't forget to mark thread as [SOLVED]