Posted to microsoft.public.excel.worksheet.functions
|
|
Text file to Excel
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
|