Text-to-Columns

[lxd]

The text-to-columns data parsing rule I create using the Excel macro recorder cannot handle exceptions well. For example, if the next data I parse contains a longer element or an indented element, the macro cuts that element into two parts and forces one part into the next column. I have to visually inspect and fix such errors which could be tedious.

How can I create a data parsing macro that is simply guided by space or tab, etc., separator and does not care about the length of an element or the number of columns the data could have. Is such a multi-purpose parsing macro possible? Thank you.

 

[poolhall]

you can do text to columns based on fixed-width data or based on a delimiter. Use the latter and also check 'Treat consecutive delimiters as one' option.

 

[AlphaFrog]

You are correct that Text-to-Columns doesn't handle exceptions well. You may have to pre-process and\or post-process your data to get it to parse the you want. Even then, depending on how "exceptional" your data is, it still may not parse 100% the way you want.

You can Pre\Post process your data using the Replace (Ctrl+H) feature or with a more sophisticated custom macro. I can't give you specifics without seeing some example data with expected results.