Splitting numbers separated by a space automatically?

**barneyc** · 07-27-2009, 04:45 AM

Good Morning,

Im after some help with the following if possible.

Currently i have a list of customer codes each seperated by a space. In the column next to them i have a percentage. (please see attached file before tab).

I would like to be able to split the cell of customer codes and transpose them so that there is one cell per customer code with the appropriate % applied (please see attached file after example tab).

At the minute a manual process is completed of sorting in length order, using the text to column function then copy paste and transpose, then copy the % in. Its quite manual process, is there an easier way?

Thanks

Barney

**DonkeyOte** · 07-27-2009, 05:11 AM

Barney, see below:

Note - test on a copied sheet first hand just in case...

Please Login or Register  to view this content.

**barneyc** · 07-27-2009, 05:59 AM

Thanks very much for your help.
The code will save me hours of work!

Cheers

Barney

**barneyc** · 12-16-2009, 06:54 AM

Morning Guys,

I have just been using the above macro kindley created by Donkeyote.

I have just noticed that it cuts off after 36 accounts.

Is there a limit to the number of characters that this code will work with?

Any help is greatly appreciated.

Cheers

Barney

**DonkeyOte** · 12-17-2009, 05:15 AM

Barney, please provide a sample file illustrating the problem - I don't really follow.

Using your earlier sample which has at times 400+ delimited codes in any given cell the code provided generates the expected output (for me at least) - ie a total of 4015 transactions out of the original 373.

I confess though I've not tested on 2003 ... I will try to later when I have the chance

**barneyc** · 12-21-2009, 06:03 AM

DonKeyote,

Thanks for your help after reading your message i realised it was when i was copying from a local version of excel to a non local version that was cutting of after a ceratin number of characters.

If i stick to using one version the macro works fine.

Thanks very much.

Cheers

Barney