Extract String

[vpranitha]

Hello All,

Looking to extract text from a string. For eg my ID is CCCC 2020-STAR A1, how do I extract CCC 2020-STAR . Greatly appreciate your assistance.

 

[jasonb75]

Perhaps =MID(A1,2,13)

With only a single example the requirement could be interpreted in many ways.

 

[mehidy1437]

Try this,

Book1
	A	B
1	CCCC 2020-STAR A1	CCC 2020-STAR
2	CCCC 2020-STAR A2	CCC 2020-STAR
3	CCCC 2020-STAR A3	CCC 2020-STAR
4	CCCC 2020-STAR A4	CCC 2020-STAR
5	CCCC 2020-STAR A5	CCC 2020-STAR
6	CCCC 2020-STAR A6	CCC 2020-STAR
7	CCCC 2020-STAR A7	CCC 2020-STAR
8	CCCC 2020-STAR A8	CCC 2020-STAR
9	CCCC 2020-STAR A9	CCC 2020-STAR
10	CCCC 2020-STAR A10	CCC 2020-STAR
11	CCCC 2020-STAR A11	CCC 2020-STAR
12	CCCC 2020-STAR A12	CCC 2020-STAR
13	CCCC 2020-STAR A13	CCC 2020-STAR
Sheet1

Cell Formulas
Range	Formula
B1:B13	B1	=MID(A1,2,SEARCH(">",SUBSTITUTE(TRIM(A1)," ",">",2))-2)

 

[sandy666]

with Power Query

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

Raw		Raw
CCCC 2020-STAR A1		CCCC 2020-STAR

 

[vpranitha]

Try this,

Book1
	A	B
1	CCCC 2020-STAR A1	CCC 2020-STAR
2	CCCC 2020-STAR A2	CCC 2020-STAR
3	CCCC 2020-STAR A3	CCC 2020-STAR
4	CCCC 2020-STAR A4	CCC 2020-STAR
5	CCCC 2020-STAR A5	CCC 2020-STAR
6	CCCC 2020-STAR A6	CCC 2020-STAR
7	CCCC 2020-STAR A7	CCC 2020-STAR
8	CCCC 2020-STAR A8	CCC 2020-STAR
9	CCCC 2020-STAR A9	CCC 2020-STAR
10	CCCC 2020-STAR A10	CCC 2020-STAR
11	CCCC 2020-STAR A11	CCC 2020-STAR
12	CCCC 2020-STAR A12	CCC 2020-STAR
13	CCCC 2020-STAR A13	CCC 2020-STAR
Sheet1

Cell Formulas
Range	Formula
B1:B13	B1	=MID(A1,2,SEARCH(">",SUBSTITUTE(TRIM(A1)," ",">",2))-2)

Try this,

Book1
	A	B
1	CCCC 2020-STAR A1	CCC 2020-STAR
2	CCCC 2020-STAR A2	CCC 2020-STAR
3	CCCC 2020-STAR A3	CCC 2020-STAR
4	CCCC 2020-STAR A4	CCC 2020-STAR
5	CCCC 2020-STAR A5	CCC 2020-STAR
6	CCCC 2020-STAR A6	CCC 2020-STAR
7	CCCC 2020-STAR A7	CCC 2020-STAR
8	CCCC 2020-STAR A8	CCC 2020-STAR
9	CCCC 2020-STAR A9	CCC 2020-STAR
10	CCCC 2020-STAR A10	CCC 2020-STAR
11	CCCC 2020-STAR A11	CCC 2020-STAR
12	CCCC 2020-STAR A12	CCC 2020-STAR
13	CCCC 2020-STAR A13	CCC 2020-STAR
Sheet1

Cell Formulas
Range	Formula
B1:B13	B1	=MID(A1,2,SEARCH(">",SUBSTITUTE(TRIM(A1)," ",">",2))-2)

Thank you

 

[mehidy1437]

You are welcome.