| 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text file to Excel
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text file to Excel
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text file to Excel
And the same is true of te formula in D2, sorry.
Keith "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 |
|
#10
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text file to Excel
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 |
| 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) | |||
Linear Mode
