Text problem - please help

[Robin]

Hi guys,

I have a spreadsheet that contains peoples names in one cell eg, "Jim SMITH" and I need to flip these around to sort them by surname.
I can do this for the above example but what about if I had "John Brian Matthew JONES" - I'm stuck.

If it is of any help, the surname is always in UPPER case.

Any brights ideas?

Robin

[crimlet]

On 2002-05-17 05:29, Robin wrote:
Hi guys,

I have a spreadsheet that contains peoples names in one cell eg, "Jim SMITH" and I need to flip these around to sort them by surname.
I can do this for the above example but what about if I had "John Brian Matthew JONES" - I'm stuck.

If it is of any help, the surname is always in UPPER case.

Any brights ideas?

Robin

If you select these cells then use the data > text to columns drop down, then select delimited and click in the space check box this will seperate them in to individual columns

[waderw]

Sounds like you have a bit of coding to do.

[Ian Mac]

On 2002-05-17 05:29, Robin wrote:
Hi guys,

I have a spreadsheet that contains peoples names in one cell eg, "Jim SMITH" and I need to flip these around to sort them by surname.
I can do this for the above example but what about if I had "John Brian Matthew JONES" - I'm stuck.

If it is of any help, the surname is always in UPPER case.

Any brights ideas?

Robin

I agree a Code based answer would be better,

BUT a formula based answer is.

If your name is in A1:

B1 = MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)+1)

Then drag/fill this to D1 (in D1 you should have =MID(C1,FIND(" ",C1)+1,LEN(C1)-FIND(" ",C1)+1))

in E1 put;

=IF(NOT(ISERR(D1)),D1&" "&LEFT(A1,(LEN(A1)-LEN(D1))-1),IF(NOT(ISERR(C1)),C1&" "&LEFT(A1,(LEN(A1)-LEN(C1))-1),IF(NOT(ISERR(B1)),B1&" "&LEFT(A1,(LEN(A1)-LEN(B1))-1))))

Drag fill as Req'd

hope this helps.

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-17 06:53 ]

[Aladin Akyurek]

On 2002-05-17 05:29, Robin wrote:
Hi guys,

I have a spreadsheet that contains peoples names in one cell eg, "Jim SMITH" and I need to flip these around to sort them by surname.
I can do this for the above example but what about if I had "John Brian Matthew JONES" - I'm stuck.

If it is of any help, the surname is always in UPPER case.

Any brights ideas?

Robin

In B1 enter and copy down:

=RIGHT(A1,LEN(A1)-SEARCH("@#",SUBSTITUTE(A1," ","@#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

where A1 houses the first full name entry.

The above assumes that the surname is preceded by a space.

Sort the names on column B.