Macro to split text into columns with limitations

[agreenleaf]

Hello. I need help in creating a Macro to split up a cell. I am constantly getting long lists of data that have to be split into two or three more cells. What I need to accomplish is to take B3 (this is where the data starts) and separate it at the 40th character without splitting up a word and moving it into column C. If there are more than 40 in Column C to do the same thing and move after the 40th character to column D. Can you help? I have attached an example of the data that needs split, column B needs split into C and D(if needed)

[Richard Buttrey]

Hi,

Your request seems somewhat contradictory. You say you want to split at the 40th character without splitting a word, (implying that you want to keep complete words). But what do you want to do if the 40th character is in the middle of a word?

If it's just a case of splitting into 40 characters use

C3: =LEFT(B3,40)
D3: =MID(B3,41,40)
E3: =MID(B3,81,40)

Rgds

[agreenleaf]

Yes, it does seem that way. I guess what I am trying to say is that I do not want over 40 characters in one cell, that is the maximum. Those functions would work, but they do not delete data after moving it, so there is still the step to delete that data after it moves. THe worksheets I get can be very long, and it a tedious job to split it up.

[Richard Buttrey]

Hi,

A function won't delete anything. A function only returns a value. If you want to automatically delete something you'll need a macro. But wouldn't that be the proverbial sledgehammer/nut?

After using the formulae I gave you why don't you first Copy the range, Paste Special Values and then just delete the original range which contains the >40 characters.

Rgds

[agreenleaf]

I am looking for a Macro to see if this could be done. I still want the data after the first 40 characters. I'd like to see the data separated into the next columns with only keeping 40 characters in each column.

[Richard Buttrey]

Hi,

You didn't say what you wanted to do with string lengths > 80. I've assumed you want these in a 3rd column.

The macro in this workbook copies the formula in C1:E1 to all the C:E cells, converts all the C:E cells to values and then removes the column B values.

I left a copy of Sheet1 in place so that you can easily restore the original data should you wish.

HTH

[agreenleaf]

Ok, it is working, the only item is that it deletes in the middle of a word. Is there a way to fix? For example if the 40th character was the i in Split, could it take the whole word "Split" and move it to the next column, instead of splitting it in the middle?

[agreenleaf]

Sorry I noticed a typo, it splits in the middle of a word not "deletes"

Also, it only runs for that selected data, is there a way to change it so that it does it for a whole worksheet? For example if I paste the rest of the data in, it could be endless, it will only do up to a certain cell.

[agreenleaf]

Ok, I figured out the second part of my question, it needs to have data in column A to work. But, I still have the question if there is a way to make it where it does not split in the middle of a word?

Thanks for helping, this will help me out a lot.