Please help: splitting...

[[email protected]]

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!
==============================================




<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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.

--
Regards,

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>