Read last element of Split array and then discard it and resize the array.

Hi,

I'm sure this is trivial to you, but I would like to change some existing code and can't work out how.

Column 2 of my sheet has thousands of cells, each of which currently has three elements, space -delimited.

The code below works fine for splitting it as needed.

Please Login or Register  to view this content.

Each cell looks like this: oranges apples pears

The first element stays in Col2, the second goes to Col3, while the final element skips one and goes to Col5.

However, I now need to modify the cells to include a final (4th) element, which will be a number.

So now it might look like : oranges apples pears 17

I therefore need to modify the code to do the following:

1. Read the number and put it in Col 20 of the same row.
2. Physically remove the number from the original cell text itself, leaving it only in Col20.
3. Discard it and re-size the array so it goes back to 3 elements again before the For loop, so the original 3 elements can be processed as previously.


Any help would be greatly appreciated.

PS. If anyone can tighten the code up that would also be appreciated. It has to go through 1000's of them!

Regards.

Hi and thanks for responding.

The trouble is that this will have to be done many times, since many columns are involved. The actual time taken doesn't matter particularly, since this is all being done to format various columns of thousands of rows, so I'd much rather have a modification to the code, so that I can just run it and wait!

Thanks again.

Well I'll be damned!

Worked perfectly. Thanks a million Norie. Nothing like a bit of lateral thinking. I feel rather stupid now. Time for some beer methinks. Thanks again.