CODEWITHSUNDEEP
excel
Ravi
Ravi

Reputation: 667

Trim string starting at comma - Excel

I have an csv sheet with data of Employee as Last Name, First Name. Below is an example. (These values are in a single cell)

Flynn, Jeremy  
Early, Allison   
Epstein, David   
Newman, Joanna  
Biord, Brooke

I need to left trim the data so that I only need the first name without any trailing space or comma.

OutPut Sample should be:

Jeremy 
Allison 
David 
Joanna 
Brooke

How can I write a formula or macro that will process the entire sheet of more that 5000 records.

Upvotes: 0

Views: 636

Answers (3)

Ramana Viswanadha
Ramana Viswanadha

Reputation: 179

Just do simple find and replace. Find ",*" and Leave Replace blank.

Upvotes: 0

user4039065
user4039065

Reputation:

As a formula in an used column to the right using the REPLACE function,

=REPLACE(A1, IFERROR(FIND(",", A1), LEN(A1)), LEN(A1), TEXT(,))

As a VBA sub procedure using Range.Replace method,

Sub firstOnly_bySel()
    With Selection
        .Replace what:=",*", replacement:=vbNullString, lookat:=xlPart
        'selection.replace(
    End With
End Sub

Select one or more cells and run the sub from the Macros dialog ([alt]+[F8])

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Formula, in an empty column put:

=TRIM(MID(A1,Find(",",A1)+1,LEN(A1)))

Where A1 is the first cell of your list. Then copy down the entire list.

If you want vba this will do it nearly instantly:

Sub firstName()
    Dim rng As Range
    With ActiveWorkbook.ActiveSheet
        'Change the Second Criterion in Each .Cells() to the column number of your data.
        Set rng = .Range(.Cells(1, 1), Cells(.Rows.Count, 1).End(xlUp))
        'Change the Offset value to the number of columns to offset the answer.
        rng.Offset(, 1).Value = .Evaluate("Index(TRIM(MID(" & rng.Address & ",Find("",""," & rng.Address & ")+1,LEN(" & rng.Address & "))),)")
    End With
End Sub

It assumes you data is in column A and it will put it in column B

Upvotes: 1

Related Questions