Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parsing Data all in one cell that is seprated by the Enter key
Hi,
I have exported about 1000 e-mails from outlook to excel. All of the information I have on customers appear in the the same cell. So I have 1000 cells on information all in one column. Here is a sample of what the exported information looks like in Excel. Everything is in one field or cell and separated by two Enter key strokes. Boxes which represent the enter key appear in excel as boxes but did not show when I posted the data into this web page. 'Name: Chuck Wildeman Address: 123 Main Street City: Atlanta State: GA Zip: 30533 Is there a way to separate or parse all of this data into separate field that all appear in one row? So I would want it to look like the following: Column B Column C Column D Column E Column F Chuck Wildeman 123 Main St Atlanta GA 30533 -- Chuck W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parsing Data all in one cell that is seprated by the Enter key
See if this works for you:
Select your column of addresses. FormatCellsAlignment tabUncheck: Wrap text EditReplace Find what: Hold down the [Alt] key, type 010, release the [Alt] key (Note: if 010 doesn't work, try 013 or 009) Replace with: : <-that's a colon (:) Click the [Replace All] button DataText-to-Columns Delimited....Click the [Next] button Delimiter: :<-Again, that's a colon....Click the [Next] button Select the fields to be skipped....Click the [Finish] button Does that help? *********** Regards, Ron XL2002, WinXP-Pro "ChuckW" wrote: Hi, I have exported about 1000 e-mails from outlook to excel. All of the information I have on customers appear in the the same cell. So I have 1000 cells on information all in one column. Here is a sample of what the exported information looks like in Excel. Everything is in one field or cell and separated by two Enter key strokes. Boxes which represent the enter key appear in excel as boxes but did not show when I posted the data into this web page. 'Name: Chuck Wildeman Address: 123 Main Street City: Atlanta State: GA Zip: 30533 Is there a way to separate or parse all of this data into separate field that all appear in one row? So I would want it to look like the following: Column B Column C Column D Column E Column F Chuck Wildeman 123 Main St Atlanta GA 30533 -- Chuck W |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parsing Data all in one cell that is seprated by the Enter key
Chuck,
The way i've done this in the past: Firstly put a sequential number sequence down column A Use the text to colums- delimited by ":" to split you fields to the right Do an edit find on e.g "name: " and replace with Blnk to remove leading characters. You now should have all the info you want clean in a verticly. with "name: " etc In the columns to the write type out the headings you want under name- then use an if statement to say if the field is "name: "then input name- accross from in address 1 if field is name- then select the row just below or "Blank" otherwise where the address is- copy the formula down- then copy & paste special- value in new sheet- then filter out the blank- you sj=hould be left with the fields as requested. REgards David "ChuckW" wrote: Hi, I have exported about 1000 e-mails from outlook to excel. All of the information I have on customers appear in the the same cell. So I have 1000 cells on information all in one column. Here is a sample of what the exported information looks like in Excel. Everything is in one field or cell and separated by two Enter key strokes. Boxes which represent the enter key appear in excel as boxes but did not show when I posted the data into this web page. 'Name: Chuck Wildeman Address: 123 Main Street City: Atlanta State: GA Zip: 30533 Is there a way to separate or parse all of this data into separate field that all appear in one row? So I would want it to look like the following: Column B Column C Column D Column E Column F Chuck Wildeman 123 Main St Atlanta GA 30533 -- Chuck W |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parsing Data all in one cell that is seprated by the Enter key
Hi Ron:
Your method works perfectly. You can also do the ALT-010 directly in the text-to-columns dialog if there any real :'s in the text -- Gary's Student "Ron Coderre" wrote: See if this works for you: Select your column of addresses. FormatCellsAlignment tabUncheck: Wrap text EditReplace Find what: Hold down the [Alt] key, type 010, release the [Alt] key (Note: if 010 doesn't work, try 013 or 009) Replace with: : <-that's a colon (:) Click the [Replace All] button DataText-to-Columns Delimited....Click the [Next] button Delimiter: :<-Again, that's a colon....Click the [Next] button Select the fields to be skipped....Click the [Finish] button Does that help? *********** Regards, Ron XL2002, WinXP-Pro "ChuckW" wrote: Hi, I have exported about 1000 e-mails from outlook to excel. All of the information I have on customers appear in the the same cell. So I have 1000 cells on information all in one column. Here is a sample of what the exported information looks like in Excel. Everything is in one field or cell and separated by two Enter key strokes. Boxes which represent the enter key appear in excel as boxes but did not show when I posted the data into this web page. 'Name: Chuck Wildeman Address: 123 Main Street City: Atlanta State: GA Zip: 30533 Is there a way to separate or parse all of this data into separate field that all appear in one row? So I would want it to look like the following: Column B Column C Column D Column E Column F Chuck Wildeman 123 Main St Atlanta GA 30533 -- Chuck W |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parsing Data all in one cell that is seprated by the Enter key
Very true, GS...
Lame excuse-My post was "pre-coffee" :\ Thanks for the reminder. *********** Regards, Ron XL2002, WinXP-Pro "Gary''s Student" wrote: Hi Ron: Your method works perfectly. You can also do the ALT-010 directly in the text-to-columns dialog if there any real :'s in the text -- Gary's Student "Ron Coderre" wrote: See if this works for you: Select your column of addresses. FormatCellsAlignment tabUncheck: Wrap text EditReplace Find what: Hold down the [Alt] key, type 010, release the [Alt] key (Note: if 010 doesn't work, try 013 or 009) Replace with: : <-that's a colon (:) Click the [Replace All] button DataText-to-Columns Delimited....Click the [Next] button Delimiter: :<-Again, that's a colon....Click the [Next] button Select the fields to be skipped....Click the [Finish] button Does that help? *********** Regards, Ron XL2002, WinXP-Pro "ChuckW" wrote: Hi, I have exported about 1000 e-mails from outlook to excel. All of the information I have on customers appear in the the same cell. So I have 1000 cells on information all in one column. Here is a sample of what the exported information looks like in Excel. Everything is in one field or cell and separated by two Enter key strokes. Boxes which represent the enter key appear in excel as boxes but did not show when I posted the data into this web page. 'Name: Chuck Wildeman Address: 123 Main Street City: Atlanta State: GA Zip: 30533 Is there a way to separate or parse all of this data into separate field that all appear in one row? So I would want it to look like the following: Column B Column C Column D Column E Column F Chuck Wildeman 123 Main St Atlanta GA 30533 -- Chuck W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Splitting Data in a Cell | Excel Worksheet Functions | |||
ENTER A FORMULA IN A CELL TO UTILIZE THE IMPORT DATA FUNCTIONS? | Excel Worksheet Functions |