Emulating Text to Columns -- Separating Data into Appropriate Colu

[Guest]

Hi,

I am working with an extremely large amount of data, and "Text to Columns"
just isn't cutting it (read: enormous, jumbled mass).

Specifically, I need to separate (first by spaces, then by commas, dashes,
semi-colons, etc.), the columns I have into a series of others that are both
meaningful and easier to manage.

As an example, I have the following:

********* ***** **
****** **
********* ***** *** **
****** ****** ****** ****
**** ***** ** ****** ***
*** ** **** ** *** ******
********** **** ***
****** ***** ******* ****

What I'd like to do is to separate the items that end with a "space, then
two characters" format (irrespective of how many other spaces are in the
string), into their appropriate columns, and then those that end in three,
four, etc. -- Unfortunately, they should never have been in the same column
in the first place.

Through my own knowledge of Excel (albeit somewhat limited), and from
reading other posts here, I've been able to come up with individual formulas
(using mid, len, find) that will separate out the "twos" with one space, and
with two (but not yet with three, etc., although I suspect if I keep working
I should be able to), but what I really want is one that will move them all
at once. Right now, I get a value error for all strings that have two spaces
when I use the formula for one, as well as when I use the formula for two on
those that only have one (or three, etc.).

Is there any way that I might be able to use a conditional statement which
counts the number of spaces, and performs the correct calculations
appropriately? I'm sure there must be, but I honestly can't think of
anything. Is there anyone out there who can help (preferably without using a
macro)?

Thanks, I'd really appreciate it.

 

[Pete_UK]

You could have something like:

=IF(MID(A1,LEN(A1)-2,1)=" ",RIGHT(A1,2),IF(MID(A1,LEN(A1)-3,1)="
",RIGHT(A1,3),A1))

The first IF looks to see if the third character from the end is a
space and if so it extracts the last two characters, the second IF
looks at the 4th character from the end and if that is a space it
extracts the final 3 characters, otherwise it returns A1 (for
example). You could extend this to extract the final 4 characters, the
final 5, the final 6 etc if the character in front is a space.

Is this what you mean?

Hope this helps.

Pete

 

[Guest]

Yes, that was more or less what I was going for, although I'm not quite sure
that that's all of it -- It's definitely returning the last two for those
that end in two, but it also seems to be returning the entire contents of the
cell for those that don't. I suppose I could do this in a multi-step
process, where I move the full cells over elsewhere, but I also need to be
able to find the "left" portion of the string that the "right" conditional
left behind.

....Any ideas?

 

[Pete_UK]

I don't know what your data represents, but as you say it is a
"jumbled mass". Presumably, if you only have 2 spaces in the cell,
this would split into 3 columns, whereas 7 spaces would split into 8,
and I assume you would want to move some columns over to help line
them up. You can count the number of spaces in the cell with:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

and by copying this down you might want to sort the data on this, so
that you get your data into blocks that will split into 3 columns,
then 4 columns etc. This might help to make some sense of the jumble.

The other formula I gave you was just an example showing how you can
extract 2-character or 3-character endings without giving rise to
errors. It was not a complete solution in its own right.

Hope this helps.

Pete

 

[Guest]

Thank you, Pete -- you have been extremely helpful. (That last one was a
pretty neat trick, too.) I'm going to tinker around with this for a little
bit more to see if I can get it exactly where I want it, but I'm definitely
worlds farther along than I was before. Thank you immensely.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thank you, Pete -- you have been extremely helpful. (That last one was a
pretty neat trick, too.) I'm going to tinker around with this for a little
bit more to see if I can get it exactly where I want it, but I'm definitely
worlds farther along than I was before. Thank you immensely.









- Show quoted text -