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