Excel Formula to Remove Everything After the First Space

[OilEconomist]

Thanks in advance and I will give feedback on whether it works or not.

I have cell entries with more than one word and I would like to extract only the first word. For example

First Word Only = First

I tried to use

=LEFT(A2, FIND( "^^", SUBSTITUTE( A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)

But it would give me everything except after the first space. The formula gave me "First Word"

 

[Toadstool]

Hi OilEconomist,

Try =SUBSTITUTE(A1,IFERROR(MID(A1,SEARCH(" ",A1),999),1),"")

The IFERROR is there so if there's only one word (like my example "Doggy" then you still get it returned.

Book1
	A	B
1	First word only	First
2	Doggy	Doggy
3	When the saints go marching in	When
4	Cats and dogs and rock and roll	Cats
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=SUBSTITUTE(A1,IFERROR(MID(A1,SEARCH(" ",A1),999),1),"")

 

[Michael M]

Maybe

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

 

[Michael M]

With error trapping

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

 

[DanteAmor]

With error trapping

Or maybe

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

 

[OilEconomist]

Toadstool
Michael M
DanteAmor
Thank you so much. I'm testing, but I think something is wrong with my data. I'm trying to use it to obtain the first word on hyperlinks. I have not tested all of you guyz recommended formulas, but I will. I just wanted to get back to everyone and say thank you so much!

 

[Michael M]

You mean you trying to extract the first word from a hyperlink ??
That is a lot different from extracting from simple text !!
What defines the first word in the hyperlinks, as most hyperlinks I've dealt with are basically one word seperated by characters like underscores.
Can you post some examples ??

 

[alansidman]

Here is an alternative solution using Power Query. Mcode follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2"})
in
    #"Removed Columns"

 

[Michael M]

Maybe this then

=LEFT(TEXT(A1,"0"),FIND(" ",A1,1))

 

[OilEconomist]

You mean you trying to extract the first word from a hyperlink ??
That is a lot different from extracting from simple text !!
What defines the first word in the hyperlinks, as most hyperlinks I've dealt with are basically one word seperated by characters like underscores.
Can you post some examples ??

Sorry for not responding sooner. Will test this weekend and get back to you.