Split Cells based on Character Lenght

**steve_aztec** · 01-31-2013, 01:21 PM

Hi everyone,

This is my first post and I just have what is probably a simple question. I am trying to write a macro that will split my cells / move the remainding characters to a different cell, based on the length of the original cell.

E.g. If cell A1 has a character count of 13, I only want it to have 10, ergo I want the remaining 2 characters to move to say... cell B1 but only if it doesn't interrupt the word. i.e. If the text reads 'the quick brown' then I don't want it to cut off mid way through the word 'brown' but rather before that.

Is this possible? Thanks.

**tigeravatar** · 01-31-2013, 01:47 PM

steve_aztec,

Welcome to the forum!
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

**RSWillis** · 01-31-2013, 03:16 PM

Sure it's possible. I like to keep the original phrase in column A, so this code puts the first 10 or fewer characters in column B, the remainder in column C.
It's mindful of spaces between words. It does nothing if the original is more than 10 characters with no spaces.
This will work for the first 5 rows, increase as needed.

Please Login or Register  to view this content.

**steve_aztec** · 02-01-2013, 06:01 AM

This works really well, thanks. What would be the equivilant code to split this down to the next row instead of the column? And if there were data in the row below then it would create a new row to account for this. Thanks again for your help; this is really helpful.

p.s. as per request I have also included a dummy workbook. (I think I've done it right anyway) example.xlsx

**RSWillis** · 02-01-2013, 08:56 AM

Well, that's quite different than the original question! This should do it:

Please Login or Register  to view this content.

Click the * below to say thank you. Mark your post as SOLVED when done.

**steve_aztec** · 02-01-2013, 09:08 AM

Thank you very much. I know the latter question was quite different from the first but it was interesting to see if it were possible and how the code works and it will come in very useful on this report I am building. Hopefully it will give me more of an insight into VBA and I'll be writing my own code in no time! Once again, thanks.