Community

I have a 250 MB account ledger that is fixed width throughout, the problem is the width changes from page to page so I can't set a standard width when importing into Excel. So, the quick of it is, is there an easy way to remove all these spaces between the columns and replace them with a simple delimiter? For each row, I just need all the whitespace to be replaced between each letter or digit, but again, it's variable throughout the whole file, i.e. 20 spaces may separate ColumnA from ColumnB the first 50 pages and then it could be 30 spaces after that, and so on.


ColumnA ColumnB ColumnC

becomes

ColumnA,ColumnB,ColumnC

However, a space may also appear in some of the row entries, i.e. "Profit Sharing", but never the columns, so maybe there is a way to search for a single whitespace surrounded by non-whitespace and replace that with something else, like an underscore.

Maybe there isn't any way of doing this actually, but incase there is, just thought I'd ask.

Thanks

You haven't explained how to distinguish between spaces within the fields and spaces between the fields.

If the fields are separated by at least two spaces, and if the separator you want is a comma, you can use this:

Find what: _{2,} [Replace the underscore with a space]
Replace with: ,

[X] Regular expression

Replace All

This assumes you are using Posix regular expression syntax:

Configure | Preferences | Editor

[X] Use POSIX regular expression syntax

Thanks Ben, it worked out great.