Split a number

[mrichmond]

I have a number in cell a1, that consists of 7032 two digit numbers, followed by a comma e.g. 01, 02, 03, 11, 91, 65, 45, 62, 83, 79 etc.

I need to split that number into groups of 8 , in one row (column A to H) so A2 = 01, B2 = 02, C2 = 03, D2 = 11, E2 = 91, F2 = 65, G2 = 45, H2 = 62, A3= 83, B3 = 79 etc (commas only appear here to show grouping, it wouldnt be in the final cells).

I will end up with 879 rows each with 8 number on.

Anyone got any idea how I can systematically do this?

[ajryan88]

Hi,

I have attached a workbook showing an example of what I think you want. My cell A1 only contains 16 values separated by ", " (note the space after the comma).

You should be able to copy this code into your workbook and make it work using a CommandButton on the active worksheet.

I hope this helps

Let me know if you need help integrating this into your workbook. I can also help you understand the code if you would like.

[ajryan88]

Actually, use this attachment instead...

[mrichmond]

Thanks for the rapid response ajryan88.

However, all the code appears to do is copy the number to row two and then nothing. Now i've just noticed that some of the numbers are actually 6d, 6f, 6c. Is this why the code doesnt work (it works perfectly in your example)?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[mrichmond]

John H. Davis, thanks very much that worked perfectly.

[ajryan88]

My code won't work if your numbers are delimited differently. Try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

in your workbook. This will work if your numbers are separated by a comma only (as opposed to a comma followed by a space like I originally assumed).

I hope this helps

[mrichmond]

ajryan thanks very much, it must have been the extra space that was killing it as making the little change made it work perfectly.

Brilliant help, two solutions, both work, couldnt be happier, thank you both.

[ajryan88]

You're welcome

Please don't forget to click on the * next to the posts of the people who have helped you, just to say thanks

Have a great day

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.