Extract text before first Space, except formula returns "BR1"

[howard]

I have text in Col J and have a formula to extract the text before the first space

If the formula returns "BR1", I want the formula amended to extract the data to the left before the last space on the right

i have manually extracted the data in Col L

extract texct based on Name.xlsx
	J	K	L
1	BR1 South 985217	BR1	BR1 South
2	BR1 East 985218	BR1	BR1 East
3	BT2 HO 987258	BT2	BT2
Sheet1

Cell Formulas
Range	Formula
K1:K3	K1	=IF(LEFT(J1,FIND(" ",J1))="BR1",LEFT(J1,9),(LEFT(J1,FIND(" ",J1))))

It would be appreciated if someone could kindly amend my formula

 

[Fluff]

How about

=IF(LEFT(J1,3)="br1",LEFT(J1,FIND("^",SUBSTITUTE(J1," ","^",LEN(J1)-LEN(SUBSTITUTE(J1," ",""))))-1),LEFT(J1,FIND(" ",J1)-1))

 

[Tom.Jones]

Word from left is always from 3 letters? then try:
=IF(LEFT(J1,3)="BR1",MID(J1,1,FIND(" ",J1,5)),LEFT(J1,3))

 

[alansidman]

An alternative is to use Power Query which is called Get and Transform Data and located on the Data tab of your version of Excel

Book6
	A	B	C	D
1	Column1		Column1.1	Column1.2
2	BR1 South 985217		BR1	South 985217
3	BR1 East 985218		BR1	East 985218
4	BT2 HO 987258		BT2	HO 987258
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

 

[Fluff]

@Tom.Jones what happens if there are more than two spaces?

 

[Tom.Jones]

The formula is for the examples given by OP.

 

[howard]

thanks for the help Guys

 

[Fluff]

Glad we could help & thanks for the feedback.