separate names in a field with a comma

I have sometimes, not all the times, 2 names in the same field spearated by a comma
How do I create another cell with only the name before the comma?


Giorgio Ferrati, Luca Cordero di Montezemoli

I want to have a cell with only Giorgio Ferrati
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This perhaps?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Or this to allow for entries without a comma:


Or you could use Text to Columns.
Data-Text to Columns.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Ups...wrong screenshot! This is the correct one... sorry!
Rob HensonFinance AnalystCommented:
How are the names getting into the same field?

If you correct the error at source, you won't have to amend it using either of the functions quoted above.

Rob H
If you want to do it through VBA, just add this to your code:
Sub NameBeforeComma()

Dim CellCont As String
     'use the cell you want where it says A1
CellCont = Range("A1")
Range("A1") = Left(CellCont, InStr(CellCont, ",") - 1)

End Sub

Open in new window

Eoin RyanI.T. ContractorCommented:
Here's a volatile function which allows you to use a function name within the sheet itself.

function name is ersplit, change to preference in the VBA function name.

Function erSplit(r1 As Range) As String
' Excel customised function
' -------------------------
' Will return string left of first comma
' inputs: Range to check
' output: String of text left of first comma
' Example: =erSplit(A1)
' Eoin Ryan Aug 08


Dim s1 As String
Dim v1 As Variant

v1 = Split(r1.Value, ",")

If UBound(v1) > 0 Then
    erSplit = v1(0)
    erSplit = r1.Value
End If

End Function

Open in new window

fitalianoAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.