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?

Example

Giorgio Ferrati, Luca Cordero di Montezemoli

I want to have a cell with only Giorgio Ferrati
fitalianoAsked:
Who is Participating?
 
StephenJRConnect With a Mentor Commented:
This perhaps?

=LEFT(A1,FIND(",",A1)-1)
0
 
StephenJRCommented:
Or this to allow for entries without a comma:

=LEFT(A1,FIND(",",A1&",")-1)

Or you could use Text to Columns.
0
 
jppintoCommented:
Data-Text to Columns.
Capturar.JPG
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
jppintoCommented:
Ups...wrong screenshot! This is the correct one... sorry!
Capturar.JPG
0
 
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.

Cheers
Rob H
0
 
ChacheYoCommented:
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

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

 volatileFunction.xls
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

Application.Volatile


Dim s1 As String
Dim v1 As Variant

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

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

End Function

Open in new window

0
 
fitalianoAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.