Formula for moving part of a name across to another cell

[temiomadeli]

Hi all,

i am trying to work out a formula to move last names across to a different cell from the below table. Please note i can only use formula and cannot use text to columns

[TABLE="width: 248"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Candidate name[/TD]
[TD] Formula[/TD]
[/TR]
[TR]
[TD]Aaron Holt[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Abhishek Wason[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adam Badcock[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adam Brown [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adam Marsden[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adrian Moore [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adrian Wright[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ahmad Refaque[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ahmed Sidot[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[sanrv1f]

Assuming the data is in A2:A10

B2

=MID(A2,SEARCH(" ",A2),LEN(A2))

and copy down

 

[barkercphs]

I am trying to create usernames by pulling the first two letters of column D, the first two letters of column C, and the last three digits of column G. Is there a way to do that?

Thank you.