Hi Rick

Many thanks for this, but the formula in C2 doesn't work. It returns a Value
error.

"Rick Rothstein (MVP - VB)" wrote:

Assuming there are always 2 sets of values after the name (for example, the
2 sets of values "6904083 PW619366C" from the first line), and assuming your
data starts in Row 2, put these formulas in the indicated cells and copy
down...

B2: =LEFT(A2,FIND(" ",A2)-1)

C2: =SUBSTITUTE(LEFT(A2,FIND("|",SUBSTITUTE(A2,"
","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-2))-1),B2&" ","")

D2: =SUBSTITUTE(LEFT(A2,FIND("|",SUBSTITUTE(A2,"
","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))-1),B2&" "&C2&" ","")

Rick


"keithobro" wrote in message
...
Hi Jarek

Here is a sample.
Name Code
MR A A ABA 6904083 PW619366C
MRS C ABBOTT 6395253 JA414952A
MRS T ABRAHAM 275880 NA434738A
MRS L ACTON 10063382 YB331839A
MRS N L ADAIR 2875886 NZ871836A
MISS G ADAM 10195035 NE712784D

I'd like Mr/Mrs/Miss in Column A, the initials in Column B and the Surname
in Column C. With the other data further along. But, as you can see, some
names have 1 initial, some have 2, maybe no initial (this would be rare)
or
more than 3 even.

Dziekuje.

Keith

"Jarek Kujawa" wrote:

could you provide a sample of yr data here?

1st record
2nd record
3rd record