# 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 
	
	
	
	
	
	




		Code:
__


=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:


*Example:**Example:.1*James, John JamesJones TomJonesLilyLily



		Code:
__


[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

Fluff said:


> 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

Fluff said:


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


----------

