Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting certain characters from a long string
Win XP Pro, Office 2003
I have received a text (txt) file with instructions to 'convert this to excel'. Each of the 765 lines of the text file is 315 characters long and consists of data about certain insurance policies. I have counted the characters as follows (applies to all lines): Policy number = Characters 1 to 15 Title = Characters 17 to 31 Last Name = Characters 32 to 70 and so on. I have copied and pasted the data into excel and now have a 'A1' (Down to A765) cell with 315 characters in it, some text, some numbers. There are now 765 rows in the file. How do I extract (to a new worksheet, in the same workbook) Characters 1 to 15 to A1 Characters 17 to 31 to B1 Characters 32 to 70 to C1 and so on? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting certain characters from a long string
=LEFT(TRIM(A1),15)
=MID(TRIM(A1),17,15) =MID(TRIM(A1),32,39) and so on then select all cells with formulas and grab the lower right corner of the rightmost cell and copy down 765 rows -- Regards, Peo Sjoblom "Stan in South Africa" <me@there wrote in message ... Win XP Pro, Office 2003 I have received a text (txt) file with instructions to 'convert this to excel'. Each of the 765 lines of the text file is 315 characters long and consists of data about certain insurance policies. I have counted the characters as follows (applies to all lines): Policy number = Characters 1 to 15 Title = Characters 17 to 31 Last Name = Characters 32 to 70 and so on. I have copied and pasted the data into excel and now have a 'A1' (Down to A765) cell with 315 characters in it, some text, some numbers. There are now 765 rows in the file. How do I extract (to a new worksheet, in the same workbook) Characters 1 to 15 to A1 Characters 17 to 31 to B1 Characters 32 to 70 to C1 and so on? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting certain characters from a long string
If you use the File/Open command in Excel it will bring up the Text
Import Wizard that will allow you to select "Fixed Width." You can then define the columns on the 2nd step by dragging with your mouse. And you can set each data type on the 3rd page of the wizard. On Jun 26, 2:37*pm, "Stan in South Africa" <me@there wrote: Win XP Pro, Office 2003 I have received a text (txt) file with instructions to 'convert this to excel'. Each of the 765 lines of the text file is 315 characters long and consists of data about certain insurance policies. I have counted the characters as follows (applies to all lines): Policy number = Characters 1 to 15 Title = Characters 17 to 31 Last Name = Characters 32 to 70 and so on. I have copied and pasted the data into excel and now have a 'A1' (Down to A765) cell with 315 characters in it, some text, some numbers. There are now 765 rows in the file. How do I extract (to a new worksheet, in the same workbook) Characters 1 to 15 to A1 Characters 17 to 31 to B1 Characters 32 to 70 to C1 and so on? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting certain characters from a long string
Stan in South Africa wrote:
Win XP Pro, Office 2003 I have received a text (txt) file with instructions to 'convert this to excel'. Each of the 765 lines of the text file is 315 characters long and consists of data about certain insurance policies. I have counted the characters as follows (applies to all lines): Policy number = Characters 1 to 15 Title = Characters 17 to 31 Last Name = Characters 32 to 70 and so on. I have copied and pasted the data into excel and now have a 'A1' (Down to A765) cell with 315 characters in it, some text, some numbers. There are now 765 rows in the file. How do I extract (to a new worksheet, in the same workbook) Characters 1 to 15 to A1 Characters 17 to 31 to B1 Characters 32 to 70 to C1 and so on? Thanks in advance. Select column A. Data menu / Text to Columns / Fixed Width |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting certain characters from a long string
Thanks for speedy answer!
"Peo Sjoblom" wrote in message ... =LEFT(TRIM(A1),15) =MID(TRIM(A1),17,15) =MID(TRIM(A1),32,39) and so on then select all cells with formulas and grab the lower right corner of the rightmost cell and copy down 765 rows -- Regards, Peo Sjoblom "Stan in South Africa" <me@there wrote in message ... Win XP Pro, Office 2003 I have received a text (txt) file with instructions to 'convert this to excel'. Each of the 765 lines of the text file is 315 characters long and consists of data about certain insurance policies. I have counted the characters as follows (applies to all lines): Policy number = Characters 1 to 15 Title = Characters 17 to 31 Last Name = Characters 32 to 70 and so on. I have copied and pasted the data into excel and now have a 'A1' (Down to A765) cell with 315 characters in it, some text, some numbers. There are now 765 rows in the file. How do I extract (to a new worksheet, in the same workbook) Characters 1 to 15 to A1 Characters 17 to 31 to B1 Characters 32 to 70 to C1 and so on? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from a long list | Excel Worksheet Functions | |||
Extracting data from a long list | Excel Worksheet Functions | |||
Extracting entries from long list | Excel Worksheet Functions | |||
Extracting a character from a string of characters | Excel Discussion (Misc queries) | |||
last digit in string gets changed to 0 (16 characters long) | Excel Discussion (Misc queries) |