Text file to Excel

[bollard]

Hello

A colleague receives, periodically, a text file. It is a huge one. It
contains name details, then a series of numerical data.

They need to convert this into an excel document, but the first obstacle
concerns the names.

The first entry, of course, is the title, followed by initials. The problem
is, the number of initials can vary from none to 4 or more.

How can we export the Text file into Excel, so that the surname column
always appears as column 3, with however many initials all in column 2? Then
the rest of the data all lnies up as well. The rest of the data is not
variable in length.

Thanks.

 

[Tieske]

sounds like almost impossible

my usual approach to these problems is to import the name as one (initials
and surname) and then use a formula in a helper column to extract the
initials/surname. An excel formula gives you much more flexibility in
splitting the field than the import function does.

regards,
Tieske

 

[bollard]

Hello

Thank you for your prompt reply.

Can you suggest a formula that will separate the title, the initials and the
surname now that they all appear in one column, please?

Thank you.

 

[Jarek Kujawa]

could you provide a sample of yr data here?

1st record
2nd record
3rd record

 

[keithobro]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[keithobro]

Hi Rick

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

 

[keithobro]

And the same is true of te formula in D2, sorry.

Keith

 

[keithobro]

Hi Rick

Now the formula in C2 just returns the title again

 

[Rick Rothstein \(MVP - VB\)]

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