Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse Data to Various Columns
Hi, I copied and pasted plain text from a source and need to parse the
columns. The problem is that the data (all in column A) needs to be parsed to different columns depending on its format. Example: Col A AAAAA ** bbbbb ** Ccccc Ddddd ** eeee Needs to be this: Col A Col B Col C Col D Col E AAAAA ** bbbbb ** Ccccc Ddddd ** eeee I was thinking using an if statement would work, i.e. if there are four "*" then ..., else... but I can't get anything to work. Please let me know if you have any other suggestion, or possible solutions. Oh, and this text cannot be opened as a file in Excel, it must be copied and pasted. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse Data to Various Columns
One play that might work. Select your data in column A. Make sure the
columns to the right are empty. Click Data/Text To Columns. Select Delimited. Under the delimiter section check "other" and put in * (unchecking other boxes). Click finish. Since we did not check "Treat Consecutive Delimiters As One" Excel will leave empty columns between your data, so you could put the "**" back in these columns and copy down. If you don't need "**", then just check the "Treat Consecutive Delimiters as One" when you go through the Text To Columns process. " wrote: Hi, I copied and pasted plain text from a source and need to parse the columns. The problem is that the data (all in column A) needs to be parsed to different columns depending on its format. Example: Col A AAAAA ** bbbbb ** Ccccc Ddddd ** eeee Needs to be this: Col A Col B Col C Col D Col E AAAAA ** bbbbb ** Ccccc Ddddd ** eeee I was thinking using an if statement would work, i.e. if there are four "*" then ..., else... but I can't get anything to work. Please let me know if you have any other suggestion, or possible solutions. Oh, and this text cannot be opened as a file in Excel, it must be copied and pasted. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse Data to Various Columns
Upon re-reading:
parsed to different columns depending on its format. If Text To Columns isn't what you want, I think you'll need to explain the format criteria a little further (and maybe post a small sample of actual data). "JMB" wrote: One play that might work. Select your data in column A. Make sure the columns to the right are empty. Click Data/Text To Columns. Select Delimited. Under the delimiter section check "other" and put in * (unchecking other boxes). Click finish. Since we did not check "Treat Consecutive Delimiters As One" Excel will leave empty columns between your data, so you could put the "**" back in these columns and copy down. If you don't need "**", then just check the "Treat Consecutive Delimiters as One" when you go through the Text To Columns process. " wrote: Hi, I copied and pasted plain text from a source and need to parse the columns. The problem is that the data (all in column A) needs to be parsed to different columns depending on its format. Example: Col A AAAAA ** bbbbb ** Ccccc Ddddd ** eeee Needs to be this: Col A Col B Col C Col D Col E AAAAA ** bbbbb ** Ccccc Ddddd ** eeee I was thinking using an if statement would work, i.e. if there are four "*" then ..., else... but I can't get anything to work. Please let me know if you have any other suggestion, or possible solutions. Oh, and this text cannot be opened as a file in Excel, it must be copied and pasted. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse Data to Various Columns
Thanks! I have tried Text to Columns but it doesn't split the columns
in the matter that I need. Here is an example of the data in Col A: ABC 5678 ** 0.1 ** http://www.somewebsite.com ABC 1234 ** http://www.somewebsite.com ABC-1234 ** 0.1 ** http://www.somewebsite.com ABC-3456 ** 0.1 ** http://www.somewebsite.com ALR-6543** 0.1 ** http://www.somewebsite.com ALR-6789 ** http://www.somewebsite.com I need all of the URLs to move to the same column. Using * as a delimiter moves the URLs with two sets of ** to the same column (E) and the URLs with one set of ** to a different column (C). And I can't use a Space delimiter because some of the ABC entries have spaces in them and would be separated. Thanks again. JMB wrote: Upon re-reading: parsed to different columns depending on its format. If Text To Columns isn't what you want, I think you'll need to explain the format criteria a little further (and maybe post a small sample of actual data). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse Data to Various Columns
Maybe sort the data, and use text to columns in stages.
If you will need to get the data back in its original order when you're all done, set up a helper column to number the data (1 through whatever - if you use a formula to do this copy/paste special to make sure its hardcoded). Then set up another new column and use =LEN(A1)-LEN(SUBSTITUTE(A1,"**","")) and copy down. This will return how many "*" are in your string. Sort the data table by this column. Now use text to columns on each group and cut/paste as needed to get the URL's in the same column. Since the data s/b grouped, you should be able to cut/paste large chunks of it and not eat up too much time. Then sort the data by the first helper column to put everything back in its original order and delete the two helper columns. " wrote: Thanks! I have tried Text to Columns but it doesn't split the columns in the matter that I need. Here is an example of the data in Col A: ABC 5678 ** 0.1 ** http://www.somewebsite.com ABC 1234 ** http://www.somewebsite.com ABC-1234 ** 0.1 ** http://www.somewebsite.com ABC-3456 ** 0.1 ** http://www.somewebsite.com ALR-6543** 0.1 ** http://www.somewebsite.com ALR-6789 ** http://www.somewebsite.com I need all of the URLs to move to the same column. Using * as a delimiter moves the URLs with two sets of ** to the same column (E) and the URLs with one set of ** to a different column (C). And I can't use a Space delimiter because some of the ABC entries have spaces in them and would be separated. Thanks again. JMB wrote: Upon re-reading: parsed to different columns depending on its format. If Text To Columns isn't what you want, I think you'll need to explain the format criteria a little further (and maybe post a small sample of actual data). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse Data to Various Columns
Actually, you don't have to run TextToColumns several times (just run it
once, then cut/paste). " wrote: Thanks! I have tried Text to Columns but it doesn't split the columns in the matter that I need. Here is an example of the data in Col A: ABC 5678 ** 0.1 ** http://www.somewebsite.com ABC 1234 ** http://www.somewebsite.com ABC-1234 ** 0.1 ** http://www.somewebsite.com ABC-3456 ** 0.1 ** http://www.somewebsite.com ALR-6543** 0.1 ** http://www.somewebsite.com ALR-6789 ** http://www.somewebsite.com I need all of the URLs to move to the same column. Using * as a delimiter moves the URLs with two sets of ** to the same column (E) and the URLs with one set of ** to a different column (C). And I can't use a Space delimiter because some of the ABC entries have spaces in them and would be separated. Thanks again. JMB wrote: Upon re-reading: parsed to different columns depending on its format. If Text To Columns isn't what you want, I think you'll need to explain the format criteria a little further (and maybe post a small sample of actual data). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) |