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

On Nov 13, 9:13 pm, A Need to Excel
wrote:
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.