I don't see how that is possible (plus it works correctly here in my copy of
Excel for the example I set up for your question). Did you copy **each**
formula, individually, from my posting and paste **each** one in the cells I
indicated?

Rick


"keithobro" wrote in message
...
Hi Rick

Now the formula in C2 just returns the title again

"Rick Rothstein (MVP - VB)" wrote:

Damn! I keep forgetting about the newsreader breaking lines at spaces.
Here
are the 3 formula assignments again, this time broken so the newsreader
won't "hide" the blanks at the end of broken lines...

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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