Splitting Uneven Text Strings

[Jim Clayton]

Hello. I have a project that I've been trying to find a solution to for a while now and I just can't seem to get it taken care of. I have a workbook, with thousands of rows of text strings. All the strings need to be separated and the contents placed into columns, identical to the Text to Column feature. The problem is that all the strings are uneven. Theres little to no consistency. I haven't been able to find a formula or function that breaks everything apart properly. It either collects too much or too little of the string, or the perfect amount, but the macro is so specific that it won't work on the data as a whole...I'd have to literally go in and make changes to account for all the variables. Does anyone know of a good solution to break up a large quantity of text strings that are uneven? The closest I've come was using the Text to Column feature two separate times with different parameters. I'm aware that this is a rather broad line of questioning but I thought I'd throw it out there. Thanks for your time.

[Fennek]

Hallo Jim, VBA and RegEx offer a lot of posibilities to handle string, but there must be some kind of systematic. Your problem is well described, but without an example of at least 10 strings, nobody can reply. regards

[Kenneth Hobs]

As Fennek said, we need example data or better yet and example workbook with before and after data.

I guess you mean break a cell's text into multiple full word text string? Even if we take a simple example, I am not sure if it covers what all you need.

e.g. The Gettysburg address (as much as will fit) in one cell with Wrap Text. Then a macro to split the "lines" to columns to the right. I have done that using a macro that used Word as an intermediate tool. It was actually designed to take wrapped text in a Textbox and then separate the "lines" to an array to be used as needed.