Extract text from cell of with numbers

[FryGirl]

I have this working part way, but of course, not complete. The original is in column A and I would like the results of column B. Column C shows the formula I tried.

bibles.xlsm
	A	B	C
1	Colossians 4:18	Colossians	Colossians
2	1 Thessalonians 1:1	1 Thessalonians
Sheet4

Cell Formulas
Range	Formula
C1:C2	C1	=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

 

[sandy666]

but B column contain number also

Extract text from cell of with numbers

so maybe:

source	text
Colossians 4:18	Colossians
1 Thessalonians 1:1	Thessalonians

 

[FryGirl]

Yes, but I should have said, separate at the number just before the colon.

 

[Rick Rothstein]

Does this do what you want...

=TRIM(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))))

 

[sandy666]

source		source
Colossians 4:18		Colossians
1 Thessalonians 1:1		1 Thessalonians
1232 Thessalonians 1:1		1232 Thessalonians
3 Thessalonians 341:1		3 Thessalonians

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"source", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}})
in
    ETBD

 

[FryGirl]

Thanks Rick. That sure does work. I also played around and came up with

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

 

[Rick Rothstein]

Thanks Rick. That sure does work. I also played around and came up with

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

If you do not mind array-entered** formulas, this one will also work...

=LEFT(A1,MAX(IF(MID(A1,ROW($1:$99),1)=" ",ROW($1:$99)))-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

NOTE: This formula assumes no chapter name is longer than 99 characters which I believe to be true.