Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text file to Excel
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text file to Excel
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text file to Excel
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. "Tieske" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text file to Excel
could you provide a sample of yr data here?
1st record 2nd record 3rd record |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text file to Excel
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text file to Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you save an excel file to be read as IBM-type text file ? | Excel Worksheet Functions | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
How can I save a file as a comma-delimited text file in Excel? | Excel Discussion (Misc queries) | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) |