Extracting text formula

[jack109]

Hi

Is there a way to extract part of text string in cell and modify it?
Using the data below as an example could a formula remove the text after "/" and before ":" with UPPER used to so that the two team names are in uppercase? I'm currently using text to columns, then a UPPER formula and copying / pasting and manually changing the "V" to "v" but was wondering if there is another way


Football / Eintracht Frankfurt v Bochum : First Half Goals 1.5
Football / PEC Zwolle v Jong FC Utrecht : First Half Goals 0.5
Football / Zaglebie Lubin v Warta Poznan : Match Odds

So I would like to turn the above to

EINTRACHT FRANKFURT v BOCHUM
PEC ZWOLLEv JONG FC UTRECHT
ZAGLEBIE LUBIN v WARTA POZNAN

Would be great if there a way to do this

Cheers

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	Football / Eintracht Frankfurt v Bochum : First Half Goals 1.5	EINTRACHT FRANKFURT V BOCHUM
3	Football / PEC Zwolle v Jong FC Utrecht : First Half Goals 0.5	PEC ZWOLLE V JONG FC UTRECHT
4	Football / Zaglebie Lubin v Warta Poznan : Match Odds	ZAGLEBIE LUBIN V WARTA POZNAN
Main

Cell Formulas
Range	Formula
B2:B4	B2	=UPPER(INDEX(TEXTSPLIT(A2,"/ "," :",1),1,2))

 

[jack109]

Fantastic but is there any way the "v" between the teams could be dropped to lowercase

 

[jack109]

Fantastic but is there any way the "v" between the teams could be dropped to lowercase

=SUBSTITUTE(UPPER(INDEX(TEXTSPLIT(A2,"/ "," :",1),1,2))," V "," v ")

I've managed to do it using this formula. Is there anything wrong with this that could provide further problems. I looks ok so far on my data

 

[Fluff]

That should be fine.