Splitting Competition names

[adinnin]

Hi There,

I wonder if anyone can help me... I need to split the following line up and make it Caps

Jack Smith v Peter Jones

I want it to look like

SMITH v JONES

I'm working on a competition and have to do hundreds of these.. is this possible in excel ?

I've managed to split the first name by finding " " but I can't manage to get any further..

any help greatly appreciated

regards

Alex

[Ron Coderre]

There's probably an easier way...but here's what I came up with:

A1: Alfred E. Neuman v Bozo T. Clown
A2: Papa Bear v Goldilocks
A3: Atila T. Hun v Captain Ahab

This regular formula parses the A1 text:

B1: =UPPER(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" v ",A1)-1)," ",REPT(" ",50)),
LEN(LEFT(A1,FIND(" v ",A1)-1))))&" v "&TRIM(RIGHT(SUBSTITUTE(RIGHT(A1,LEN(A1)
-FIND(" v ",A1)-2)," ",REPT(" ",50)),LEN(RIGHT(A1,LEN(A1)-FIND(" v ",A1)-2)))))

Copy that formula down through B3.

With the above examples the formulas return:
B1: NEUMAN V CLOWN
B2: BEAR V GOLDILOCKS
B3: HUN V AHAB

I hope that helps.

[outofthehat]

If it's always firstname lastname (i.e. always 4 spaces), then this works:

=UPPER(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))&"v "&RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)))

which roughly translates as
UPPER: Convert to upper case
MID: Take the text out of the middle of A1, starting at the first space+1 character, length to extract is position of second space minus position of first space
&"v " puts the v back in
RIGHT: take the right-hand n characters from A1, where n is calculated by length LEN minus the position of the 4th space.

[outofthehat]

...and if you want a lowercase v in the middle:

=UPPER(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))
&"v "
&UPPER(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)))

[martindwilson]

slightly shorter should work with multiple initials

=TRIM(UPPER(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1," v ",REPT(" ",50)),1,40))," ",REPT(" ",40)),20)))&" v "&UPPER(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",25)),25)))

[adinnin]

Thankyou all so much..

I didn't expect so many responses so quickly..

you have saved me hours of work !!

cheers

Alex

[Marcol]

This fearsome object seems to work

=UPPER(RIGHT(LEFT(A2,FIND(" v ",A2,1)-1),LEN(LEFT(A2,FIND(" v ",A2,1)-1))-LOOKUP(LEN(LEFT(A2,FIND(" v ",A2,1)-1)),FIND(" ",LEFT(A2,FIND(" v ",A2,1)-1),ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(LEFT(A2,FIND(" v ",A2,1)-1)),1))))))&" v "&UPPER(RIGHT(A2,LEN(A2)-LOOKUP(LEN(A2),FIND(" ",A2,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A2),1))))))

It returns
SMITH v JONES
NEUMAN v CLOWN
BEAR v GOLDILOCKS
HUN v AHAB

From

Jack Smith v Peter Jones
Alfred E. Neuman v Bozo T. Clown
Papa Bear v Goldilocks
Atila T. Hun v Captain Ahab

From one you made earlier Ron