I want to extract text before a comma, or space or just the first name from a string

[Sean15]

I want to extract text before a comma, or space or if condition 1 or 2 is not met return text in cell:

Example:
James, John
Jones Tom
Lily

Expected result:
James
Jones
Lily

=LEFT(A1,(FIND(" ",A1,1)-1)) extracts text before space
=LEFT(A1,(FIND(", ",A1,1)-1)) extracts text before comma
I’m not sure what formula returns Lily

Could you help me combine formulas to get the THREE expected results?

Thank you for your help.


Regards,

Sean

 

[t0azt]

Try

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

 

[Fluff]

How about
=LEFT(A1,FIND(" ",SUBSTITUTE(A1,","," ")&" ")-1)

 

[Sean15]

@t0axt, @Fluff

Thanks very much.

@t0azt - just curious, why do we need two IFERROR?


Regards,

Sean


Regards,

Sean

 

[Fluff]

You're welcome & thanks for the feedback

 

[t0azt]

=IFERROR(LEFT(A1,(FIND(",",A1,1)-1)),LEFT(A1,(FIND(" ",A1,1)-1))) has the potential to return a #VALUE ! error if there is neither a comma or a space like in the case of Lily. In the case of Lily, for example, the formula once evaluated looks something like this:
IFERROR(IFERROR(#VALUE !,#VALUE !),Lily). The inner IFERROR returns #VALUE ! so the formula ends up being IFERROR(#VALUE !,Lily) which will return Lily.

...sorry I'm not great at explaining things, I don't know if that made sense.

 

[sandy666]

maybe easier will be with PowerQuery:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Example:[/td][td][/td][td=bgcolor:#70AD47]Example:.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]James, John [/td][td][/td][td=bgcolor:#E2EFDA]James[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jones Tom[/td][td][/td][td]Jones[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Lily[/td][td][/td][td=bgcolor:#E2EFDA]Lily[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Example:", Splitter.SplitTextByAnyDelimiter({" ",","}, QuoteStyle.Csv)),
    ROC = Table.SelectColumns(Split,{"Example:.1"})
in
    ROC[/SIZE]

 

[Dannnn]

How about
=LEFT(A1,FIND(" ",SUBSTITUTE(A1,","," ")&" ")-1)

This is so cool. this made my day. Thanks. Best wishes to you and yours.

 

[Fluff]

Glad it helped & welcome to MrExcel.

 

[Dannnn]

Glad it helped & welcome to MrExcel.

I was using two different formulas in two columns the first formula changed the comma to a space, then I picked the letters before the space for my word. But your formula works well and was exactly what I needed. While almost exactly. I still have to copy value do another column manually I'll make a new thread about that. thanks again.