Parse Data to Various Columns

[jkareem]

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.

 

[JMB]

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.

 

[JMB]

Upon re-reading:

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).

 

[jkareem]

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]

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.

 

[JMB]

Actually, you don't have to run TextToColumns several times (just run it
once, then cut/paste).