Please help: splitting...

[dervishme]

I need to have the values in these cells "splitted":

A1: Red 8 Y 48
A2: Magenta 2 Y 16
A3: Green 30
A4: Blue 12

to something like these:

B1: Red C1: 8 D1: Y E1: 48
B2: Magenta C2: 2 D2: Y E2: 16
B3: Green C3: D3: E3: 30
B4: Blue C4: D4: E4: 12

Please notice A3 and A4 have no number before the Y so the only number
after the color name must go to column E.

This is really a tough one for me. Any help from you experts would be
greatly appreciated.

Henry

 

[Jacob]

What kind of character is in between the values? i.e. If it is a TAB
than use the Search function for the tab character w/in a MID function.

 

[RagDyer]

You could try:
<Data> <Text To Columns> <Delimited> <Next>

Then check "Space",

And make sure that
"Treat Consecutive Delimiters As One"
Is *NOT* checked,
Then <Finish>

This *might* work for you, depending on your actual data, where you might be
able to delete some of the returned empty columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[dervishme]

Thanks, RagDyer!

It worked perfectly. I did have to check "Other" instead of "Space" to
bring the values w/o the Y to the right columns...

I spent a great deal of time trying to come up with a formula and your
totally unexpected solution just blew me away.

I just love the internet. What a great community!

Henry

 

[RagDyer]

Appreciate the feed-back.