Extracting data from a variable length string

[austenr]

Suppose you have the following:

smith - xx - john
anderson - xx - april
williams - xx - john

What i want to do is to have it end up like

xx john smith
xx april anderson
xx john williams

You cant use LEFT or MID or RIGHT afaik because of the length of the string.

Anyone have any suggestions on how to accomplish this?

The string would be in A1, A2, etc.

[austenr]

ok so i got this far:

anderson - xx - april

using =LEFT(A1, FIND("-",A1)-1 )

i can get Anderson

how do you get April in the next adjoining cell?

[SamT]

Function Recombine(strIn As String)AS String
'Assumes strIn is a 5 part string like "smith - xx - john"
'Returns a 3 part string like "xx john smith"

Dim Tmp
tmp = Split(strIn, "-")
Recombine = Trim(Tmp(1) & tmp(2) & tmp(0))
End Function

[p45cal]

try:
=MID(A1,FIND(" - ",A1)+3,FIND(" - ",A1,FIND(" - ",A1)+1)-FIND(" - ",A1)-3)
to get the xx

=MID(A1,FIND(" - ",A1,FIND(" - ",A1)+1)+3,999)
to get the john

=LEFT(A1, FIND(" - ",A1)-1 )
to get the smith.

[austenr]

try:
=MID(A1,FIND(" - ",A1)+3,FIND(" - ",A1,FIND(" - ",A1)+1)-FIND(" - ",A1)-3)
to get the xx

=MID(A1,FIND(" - ",A1,FIND(" - ",A1)+1)+3,999)
to get the john

=LEFT(A1, FIND(" - ",A1)-1 )
to get the smith.


Thanks guys. Both work well.